In the previous article, we learned how to download historical end-of-day price data from Yahoo Finance into Excel. If you haven’t read that article, I suggest that you start there. Another very popular free source for this information is Google Finance. In this article, we’ll add onto the program that we already built to be able to download the data from Google.
Get CSV File Download Link Address
Just like with Yahoo, the first thing that we need to get is the specific csv file download link address for the stock and date range that we’re interested in. For this example, we will use the same desired data set – three years of daily prices for Apple (Jan 1, 2014 to Dec 31, 2016). To do this, go to www.google.com/finance, and then enter Apple’s ticker symbol AAPL into the Search Finance input box at the very top of the page.
This takes us to the main Google Finance page for Apple. Click on the Historical prices link at the top left-hand side of the page.
We now see the complete list of historical prices for Apple. To get the specific date range that we want, click on the data input boxes at the top right of the data table and enter the start date (Jan. 1, 2014) and the end date (Dec. 31, 2016). Notice that Google does not give you an option to change the frequency of the data. It only displays daily prices.
Once the inputs are correct, hit the Update button. This updates the data listed on the page to only be for the date range that we entered. Notice to the right of the Update button is a historical price chart. Below this is a link that say’s Download to spreadsheet. This is the download address that we want to get. If you left click on this, it will download the data displayed and save it as a comma-separated values or csv file.
At this point, we could just hit the link, save the file, and then open it using Excel. But instead of doing this manually, we want to setup Excel to do this for us automatically, just like we did for the Yahoo Finance download. That is why we need the file download link. To get it, right click on the link and select Copy address link. If you paste this into a text editor, it should look something like this:
http://www.google.com/finance/historical?cid=22144&startdate=Jan+1%2C+2014&enddate=Dec+31%2C+2016&output=csv
Important note: If you do not see this Download to spreadsheet link, you will not be able to download the data for the stock you have entered. This can be the case for some stocks on foreign exchanges depending on your location.
Automatically Get External Data into Excel
In the previous article, we recorded a macro of us manually retrieving data from a specific csv file link address. This macro entered the downloaded data into the spreadsheet for us. Since we will add onto the Excel file that we previously created, we have the base macro code to do this. So if you haven’t already, open up the Excel file that we created for downloading historical prices from Yahoo.
Get Google Historical Prices Sub-Routine
Once you open the previous Excel file, go to the Visual Basic screen (ALT + F11). Paste this code into the Visual Basic Editor screen:
Sub get_google_historical_prices(ticker As String, start_date As Date, end_date As Date)
'Sub-routine saves the daily historical price data for the ticker and dates desired
'It saves the data on the active sheet when called
'Get desired URL structure for Google Finance
Dim str_start_date As String
str_start_date = MonthName(Month(start_date), True) & "+" & Day(start_date) & "+" & Year(start_date)
Dim str_end_date As String:
str_end_date = MonthName(Month(end_date), True) & "+" & Day(end_date) & "+" & Year(end_date)
Dim google_url As String: google_url = "http://finance.google.com/finance/historical?q=" & ticker & _
"&startdate=" & str_start_date & _
"&enddate=" & str_end_date & _
"&output=csv"
'Save data from csv file to the active sheet
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & google_url, Destination:=Range("$A$1"))
.Name = ticker
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=False
End With
End Sub
Let’s walk through this code to better understand what it is doing. First off, it is very similar to the get_yahoo_historical_prices sub-routine. Notice that for the Google code, we do not have frequency as an input. This is because that for Google downloads we cannot change the frequency of the data – it will always be daily prices.
The code defines str_start_date and str_end_date variables. These variables are used to help get the specific Google download link address. Here is the link that we copied earlier from Google:
http://www.google.com/finance/historical?cid=22144&startdate=Jan+1%2C+2014&enddate=Dec+31%2C+2016&output=csv
Notice that the parts for the start date and end date display the month as the abbreviated month’s name (Dec), not the month number (12). This is how the code formats the start_date and end_date entered. It uses the MonthName built-in function to convert the month number to the abbreviated name (‘Jan’ for January and ‘Dec’ for December). It also uses the Month, Day, and Year functions to strip those pieces out of the dates. This results in the following values in our case:
- str_start_date = “Jan+1+2014”
- str_end_date = “Dec+31+2016”
One thing to point out is that in our copied link it also contained a “%2C” after the start and end date’s day. This isn’t necessary, so we will leave this out. After the dates are formatted correctly for the download URL, the sub-routine defines the google_url variable. Let’s break down the copied download link address to better understand the URL structure:
http://google.com/finance/historical?
cid=22144&
startdate=Jan+1%2C+2014&
enddate=Dec+31%2C+2016&
output=csv
The first line will always be the same. The second line shows “cid=22144”. This is an ID that Google uses to identify Apple’s stock. An easier way for us to identify the stock that we want is by the ticker symbol. We will change this line to be “q=AAPL”. The “q” tells Google that we are passing the ticker symbol, not an id. This is the trickiest part of automating the download from Google. The third and forth lines are just the start and end date strings that the code already defined. That leaves the last line that is also always the same, regardless of the ticker or dates entered. All of these lines combined is what the google_url variable is storing.
Now that the code knows the link to the csv text file download, it uses the same block of code that we recorded for the Yahoo download to add a query table to the active sheet. This part of the code is what actually retrieves the data from Google’s website and saves it on the active sheet. It uses the google_url variable instead of the hard coded address that we copied in Yahoo download example. It also only has the query table settings that are necessary.
Google Prices Sub-Routine
Now that we have a sub-routine to get the data that we want, we need another sub-routine to get the user inputs and pass this information to the previous sub-routine. We’ll call this new sub-routine google_prices. Copy the code below and paste it into the code area of the Visual Basic screen. Here’s the code:
Sub google_prices()
'Sub-routine retrieves the user inputs from the "Inputs" sheet
'It then passes this into the get_google_historical_prices function
'Save necessary input variables
Dim ticker As String: ticker = Sheets("Inputs").Range("ticker").Value
Dim start_date As Date: start_date = Sheets("Inputs").Range("start_date").Value
Dim end_date As Date: end_date = Sheets("Inputs").Range("end_date").Value
'Activate appropriate sheet to put price data
ThisWorkbook.Sheets("Data").Activate
'Delete any data currently on the sheet
Cells.Delete
'Call the Google sub-routine
Call get_google_historical_prices(ticker, start_date, end_date)
End Sub
So what it is this code doing? First, it defines the three user inputs from the Inputs sheet (Remember that the frequency input doesn’t apply to the Google download). It references the named ranges on this sheet that we created. Next, it activates the Data sheet and deletes any values on the sheet. This makes sure that no previously download data will accidentally be left on the sheet. Last, it calls the get_google_historical_prices sub-routine that we just created, and it passes the three user inputs into the sub. This call statement will run the other sub-routine.
Input Sheet
The Inputs sheet should already have four named ranges:
- ticker
- start_date
- end_date
- frequency
The only change we need to make on this sheet is to add another button to run the code to get the historical prices from Google (instead of Yahoo). Copy the Get Prices From Yahoo button and paste a copy next to it. Rename it Get Prices From Google. Finally, right click on the button, select Assign Macro, and select the new macro that we created (google_prices).
Test It Out!
Let’s test out this new macro. Go to the Inputs sheet and enter the inputs in the appropriate cells. Click on the new macro button. It should run the google_prices sub-routine and download the historical prices to the Data sheet. Here’s what the Data sheet should look like:
Awesome! We just downloaded historical price data from Google Finance into Excel. Enter some different ticker symbols and dates, and test it out.
If you look at the data downloaded from Google Finance, you can see that there are only 6 columns of data compared to 7 columns of data from Yahoo Finance. The difference in the two downloads is that Google Finance adjusts all of the prices for dividends paid, stock splits or reverse splits, etc. Yahoo Finance leaves the original Open, High, Low, and Close prices, and adds a column for the adjusted closing price. This is why Yahoo’s data has the extra column named Adj Close.
Like I mentioned in the previous article, if you would like to download this Excel file for free, you are in luck. You can get it below with many extras also thrown in. In part 3 of this series, I’ll show you how to use what we built here to download the historical prices for a list of stocks.