In the previous two articles, we learned how to download historical end-of-day price data into Excel for any stock that we wanted. We used Yahoo Finance and Google Finance as the sources of the data. In this article we will expand on the previous programs to be able to download the data for a list of stocks. Go ahead and open up the Excel workbook that we’ve built so far. If you haven’t read the other articles, I suggest that you start there.
Changes to the Inputs Sheet
The first thing that we need to do on the Inputs sheet is to delete the single Ticker Symbol input and named range. Instead we will have a list of ticker symbols. Select the row for the ticker symbol, right click on it, and then hit Delete. To delete the named range, go to the Formulas tab on the ribbon, and click on Name Manager under the Defined Names section. Select the ticker named range, then hit Delete.
Now we need a place to put the list of stocks. Let’s put the header List of Tickers in cell A8. We will then put the list of ticker symbols in column A, starting in row 9. For this example, I’m going to enter the 30 current stock symbols for the companies in the Dow Jones Industrial Average. The symbols are: AAPL, AXP, BA, CAT, CSCO, CVX, KO, DD, XOM, GE, GS, HD, IBM, INTC, JNJ, JPM, MCD, MMM, MRK, MSFT, NKE, PFE, PG, TRV, UNH, UTX, V, VZ, WMT, and DIS. Here’s what my Inputs sheet looks like:
Changes to yahoo_prices Sub-Routine
We need to change this macro so that it knows where to find the list of ticker symbols, then have it loop through the list and call the get_yahoo_historical_prices sub-routine for each ticker. Go to the VBA screen by either using the ALT + F11 shortcut or by clicking on the Developer tab on the ribbon, then clicking on Visual Basic under the Code section. Find the previous yahoo_prices sub-routine and replace it with the following code. Remember to scroll all the way to the right to copy all of the code.
Sub yahoo_prices()
'Sub-routine retrieves the user inputs from the "Inputs" sheet
'It then passes this into the get_yahoo_historical_prices function
'Save necessary input variables
Dim ticker As String
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
Dim frequency As String: frequency = Sheets("Inputs").Range("frequency").Value
'Get last row with a stock ticker
Dim last_row As Integer: last_row = Sheets("Inputs").Cells(Rows.Count, 1).End(xlUp).row
Dim total_downloads As Integer: total_downloads = last_row - 8
Dim row As Integer
'Loop through all stocks listed
For row = 9 To last_row Step 1
'Get current ticker symbol
Sheets("Inputs").Activate
ticker = Cells(row, 1).Value
'Update status bar
Application.StatusBar = "Downloading historical prices from Yahoo for " & ticker & _
". Stock " & row - 8 & " of " & total_downloads & "..."
'See if a sheet named the current ticker symbol exists
If Evaluate("ISREF('" & ticker & "'!A1)") Then
'Sheet named with ticker symbol exists
'Select sheet and delete all contents
Sheets(ticker).Select
Cells.Delete
Else
'Create new sheet and add it to the end of all sheets
Sheets.Add After:=ThisWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
'Rename new sheet to be the current ticker symbol
ActiveSheet.Name = ticker
End If
'Call the yahoo sub-routine
Call get_yahoo_historical_prices(ticker, start_date, end_date, frequency)
Next row
'Clear status bar
Application.StatusBar = ""
End Sub
Let’s walk through this new code and describe how it is different than your old code. First off, we still define a variable called ticker, but it is no longer set to the value from the Ticker named range. We will define this later. After the start_date, end_date, and frequency variables are defined, we added logic to get the last row on the Inputs sheet that has a ticker symbol. This is done by the following line of code:
Dim last_row As Integer: last_row = Sheets(“Inputs”).Cells(Rows.Count, 1).End(xlUp).row
This function goes to the very last row on the spreadsheet (Rows.Count), then moves up in column 1 (or A) until it finds a cell that is not blank (.End(xlUp)). This is a built-in Excel function. You can do this manually by holding CTRL + SHIFT + the up or down arrow key. The row that it stops at is saved as the last_row variable (.row). In our example, we entered 30 ticker symbols that started in row 9 and ended in row 38. This means that the last_row variable was set to 38.
Next it calculates the total number of downloads required by subtracting 8 from the last_row variable. We’ll use this later to help show what the current status of the program is.
Now that the sub-routine knows the first and last row with ticker symbols, it can loop through these and download the historical price data for each ticker. To do this a row variable is defined and the program loops from row 9 to the last_row via the following statement:
For row = 9 To last_row Step 1
The Step 1 comment is not necessary, but makes it clear how the row variable will be changed for each iteration. If you wanted the loop to skip every other line you would put Step 2. If you wanted to start on the last row and go up, you would put Step -1.
Now the sub-routine is looping through the ticker symbol rows. For each row, we need to update the ticker variable to be the value in the active row in column 1. Next in the code is an update to the status bar. The status bar is at the very bottom of the Excel sheet, just below the list of sheet names. This can be very helpful to tell the user where a sub-routine is currently at. In our case, the status bar will tell the user that the program is “Downloading historical prices from Yahoo” for the current ticker symbol. It also states what number of the total number of downloads it is currently on. For example, AXP is the second ticker listed, so it shows that it is on “Stock 2 of 30”.
Next the macro checks to see if a sheet exists in the Excel workbook that has the name of the current ticker symbol. It does this with the following line:
If Evaluate(“ISREF(‘” & ticker & “‘!A1)”) Then
The Evaluate VBA function allows you to perform built-in Excel worksheet functions in VBA code. To find out more, here is a good reference that describes some of the things you can do with this function.
In our case it “evaluates” whether there is a valid reference for cell A1 on a sheet with the name of the ticker value. If it is a valid reference, a sheet named the ticker symbol exists, and the function will return True. If not, a sheet named the ticker symbol doesn’t exist, and the function will return False.
In the case where a sheet already exists with the current ticker symbol name, the code selects this sheet, then deletes all values on the sheet. If this sheet doesn’t exist, the code adds a new sheet at the very end of the worksheet list, then renames it to be called the current ticker symbol.
At this point the program has either created a new sheet named for the current ticker symbol, or it activated this sheet and deleted its contents. The last step of the for loop is to call the get_yahoo_historical_prices sub-routine that we have already created. The Next row statement takes the code back to the top of the for loop and repeats the process for the next ticker symbol. It does this until the last ticker symbol is processed, then the code exits the for loop. At this point, it resets the status bar to be blank and the sub-routine ends.
Changes to google_prices Sub-Routine
The changes we need to make to the google_prices sub-routine are identical to the yahoo_prices sub-routine changes already described. The only differences is that for the status bar update, it tells the user that it is “Downloading historical prices from Google”. It also needs to call the get_google_historical_prices function instead of the one for Yahoo’s prices. Replace your old code for this sub-routine with the following:
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
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
'Get last row with a stock ticker
Dim last_row As Integer: last_row = Sheets("Inputs").Cells(Rows.Count, 1).End(xlUp).row
Dim total_downloads As Integer: total_downloads = last_row - 8
Dim row As Integer
'Loop through all stocks listed
For row = 9 To last_row Step 1
'Get current ticker symbol
Sheets("Inputs").Activate
ticker = Cells(row, 1).Value
'Update status bar
Application.StatusBar = "Downloading historical prices from Google for " & ticker & _
". Stock " & row - 8 & " of " & total_downloads & "..."
'See if a sheet named the current ticker symbol exists
If Evaluate("ISREF('" & ticker & "'!A1)") Then
'Sheet named with ticker symbol exists
'Select sheet and delete all contents
Sheets(ticker).Select
Cells.Delete
Else
'Create new sheet and add it to the end of all sheets
Sheets.Add After:=ThisWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
'Rename new sheet to be the current ticker symbol
ActiveSheet.Name = ticker
End If
'Call the Google sub-routine
Call get_google_historical_prices(ticker, start_date, end_date)
Next row
'Clear status bar
Application.StatusBar = ""
End Sub
Summary
In the past 3 articles, we have written Excel macros to automatically download historical end-of-day price data for a list of stocks from Yahoo and Google Finance. If you would like to download a copy of this Excel workbook with several extra features also included, just click on the link below.