Find and download historic stock data
We recommend Yahoo! Finance to look up and download historical data for stocks. The offering is extensive and has many different layers of information. But the recommendation is based on how simple it is to download the data there.
There might be other sources for public stocks or funds. But so far we found that Yahoo! Finance is the easiest site to use for this purpose. If you have a better tip or alternative please send us the link and we include additional options in this tutorial.
But back to Yahoo! Finance. For demonstration purposes, let's take the stock of Amazon (AMZN). The company has gone through an amazing development, from a pioneering online bookseller to global e-commerce powerhouse. For the last few years the company is pushing forward on digital entertainment (Amazon Prime Video, Prime Music) and - amazingly - cloud computing with its Amazon Web Services offering (AWS). In short: This is a unique development, how is this reflected in the stock performance and how can we tell a story from IPO to today, using Datawrapper?
Here are the steps to search for the Amazon stock data and firstly download the data to a spreadsheet software for a brief preparation before importing that data into Datawrapper.
(1) Go to Yahoo! Finance
(2) Search for the stock. In our case we search for Amazon (AMZN). Historical data for the particular stock is then found in the menu (red circle below).
(3) Choose the time range you want. This can be a week, a month, year or even all the data since the day of first trading of the stock.
A brief excursion here: Every day would result in too much data. From 1997 to 2016, spanning roughly 18 years we would get almost 5.000 lines of data. So, our choice here will be the weekly data, which provides more than enough data points.
Simply click on "Historical Data"
Then define the time period - in our case we simply click on "MAX"
Click "Done", then "Apply" and then finally "Download Data". This is all we needed from Yahoo! Finance.
Step 2: Preparing the data
(Hint: If you are familiar with spreadsheets, you can skip this part. What we do is a bit of cleaning, in order to the copy & paste the data into Datawrapper)
We firstly upload the data again into a spreadsheet. This will be Excel for most users. In order to share the data with you we selected Google Sheets, in this particular case. By and large the next refining steps might differ slightly in Excel, but are essentially very similar or even the same.
(4) As a first step of preparing the data for the visualization we need to upload the data correctly into the spreadsheet. We downloaded the data from Yahoo as a .csv, which means we should not simply open it in either Excel or Google Sheets. Instead we use "Import".
In Google Sheets "Import" is under the menu "File"
(5) Your data should now be imported. Just in case, here is a link to the Google sheet with the data we will use in this example: Amazon Historic Stock Prices.
(6) Tip: Create a copy of the data. Here is useful trick for working in any spreadsheet software: Always make a copy of the imported data and make this a habit. The reason is simple: In the next steps we will re-sort the data, apply changes, etc. By keeping the original data (at best including a link to the original source) we can always be sure that we can check back, just in case we make a mistake. In this particular example it's not critical and neither is there a big chance for making big mistakes. But make this a habit. This will eventually save you a lot of time in some of your data explorations.
To make the copy we simply open a new tab at the bottom of the tutorial and rename it. It should look like this. These tabs at the bottom of the spreadsheet are working in the same way in both Google Sheets and Microsoft Excel.
(7) Preparing the data (in Google Sheets)
Wether we do this in the spreadsheet or directly in Datawrapper depends. Datawrapper has some options to select data, etc. - but for larger re-sorting, re-formatting we would always recommend a proper spreadsheet software.
The following tips are specifically for Google Sheets.
(8) Freeze the first row with the titles for each column This is to ensure that the first row stays in place, even though we re-sort the data. Another benefit is that if we scroll down in the spreadsheet, the title header will always be visible. titles stays in place, even when we start sorting the data.
Go to "View", then "Freeze", then select "1 row".
(9) Which data should we use: Open, close or average?
When we downloaded the stock data we got several columns: Yahoo! Finance gave us the historic data for open and close for each period we wanted (day, week, month).
So we several prices for the stock:
- Open: The price of the stock at the beginning of the trading day
- Close: The price for the stock at the end of the trading day
- High: The highest price of the stock during the trading day
- Low: The lowest price of the stock during the trading day
- Volume: This is usually the total trading volume for the day
- Adj. Close: The closing price for the day, adjusted for corporate actions such as a stock split or paying dividends.
For our line chart we will use the "Adjusted Close" price. Given that we are looking we could use "Open" and "Close" too. What is important here: Should we later look at a particular period of the stocks performance, e.g. one or several years or a specific shorter period we whould always stick to using the Adjusted Close. In general consistency is important, but we can achieve that with either data column.
(10) Hide columns
We don't need all the data. We could delete it - given that we are working only with a copy of the data we imported. But an even easier option is to simply "hide" the columns we don't need.
In Google Sheets there is a little button on top of the column where we can get to a menu to do this. First marke the columns you want to hide, then select any of the little drop-down buttons and select "Hide columns..." from the menu.
There are now small arrows between the columns to indicate hidden columns. One click and they are shown again.
For us the cleaned data is just right to be imported into Datawrapper now. What we described here is quite typical and actually a relatively easy case on data preparation. Often the data is not as good and not as consistent and needs more cleaning and preparation. But that highly depends.
In our case, we now go to Datawrapper for the line chart visualization: