Excel In Trading

  • Home
  • About
  • Contact

Custom Intraday Bars in Quantopian

October 17, 2017 By Aaron Eller

When I first got started using the platform Quantopian, I was like a kid in a candy store. I could pretty quickly test different trading ideas that I couldn’t previously do very easily – especially when it came to using intraday equity prices.

kid with candy

There was one thing, however, that I really struggled with at first. Quantopian gives you access to daily bars and one-minute bars. That’s great, but I wanted to look at 30-minute and 60-minute bars. How do I do that? I knew I should be able to take the one-minute bars and resize them to be larger multi-minute bars (called downsampling). For some reason, how to do this wasn’t being shared (as far as I could tell). It took me longer than it should’ve to figure this out, so hopefully I can save you some time.

The best method that I found to solve this problem was using the Pandas DataFrame resample function. One reason for this is that when we call the data.history function in Quantopian to get one-minute bars, it already returns a pandas DataFrame object.

As of this writing, pandas 0.18.1 is the current version being used by Quantopian (though there are newer versions available). Here’s a link to the resample function’s documentation. When I look at most documentation like this, I want to pull my hair out. Why do they make it so complicated?

why?

I read this, and I still don’t know how to use the function. And it even gives an example of downsampling one-minute data to 3-minute bins. What it doesn’t do, is give an example showing how all the input parameters work. And that’s the problem. Because the input that is most important in our case is the ‘base’ parameter, which states:

For frequencies that evenly subdivide 1 day, the “origin” of the aggregated intervals. For example, for ‘5min’ frequency, base could range from 0 through 4. Defaults to 0

That’s the best description they could come up with? I still don’t know what the heck that means, and I’m a nerd.

So, I approached this like I do engineering problems – I ran some experiments to figure it out for myself. I setup a test algorithm to do the following:

  • Have a variable to enter the desired intraday bar that I want (e.g. 5 minute)
  • For simplicity, only look at data for a single equity (e.g. AAPL for Apple)
  • Log every one-minute bar for the equity, so I can easily check if the intraday bars are correct
  • Resample and log the custom minute bars

The initialize function

So the first step was to write the initialize function that gets called at the very start of the algorithm. See below that I set the desired intraday bar to the variable context.intraday_bar and the stock that I want to track to the variable context.asset. In this example, I used 5 minutes as the intraday bar since it would be easy to check this manually (comparing each bar output to the previous 5 one-minute bars).

The tricky thing to do here was run a function every 5 minutes of the trading day to calculate the custom intraday bars? The US equity markets are open from 9:30am to 4:00pm EST. That’s 6.5 hours or 390 minutes. So I created a for loop, iterating the variable i from 1 to 390 (line 24).

Side note, why do programmers like to use the variable i so much for looping integers? I wondered the same thing, and this was the best answer I found. I’m so glad I learned Python and never used Fortran again after college.

Back on task – i loops from 1 to 390 (total number of minutes in the trading day). The easy way to get it to call a function every 5 minutes is to calculate the modulus (or remainder) of the quotient i/5. To get the value of the modulus only, use i % 5. What we want is when the modulus equals 0, meaning that 5 divides evenly into the value of i. This will be true for i = 5, 10, 15, etc, and is the number of minutes after the market open that we need to calculate a new intraday bar. Line 25 checks for a zero remainder, and when it’s true, line 26 sets up a scheduled function to run that specific minute after the open. The function that is called is one we’ll define later, get_intraday_bar.

"""
Intraday bar test
By: Aaron Eller
aaron@ExcelInTrading.com
www.ExcelInTrading.com
"""

import pandas as pd

def initialize(context):
"""
Quantopian function called once at the start of the algorithm.
"""
# Verify version of Pandas used
log.info('Current version of pandas is {}'.format(pd.__version__))

# Asset to trade
context.asset = sid(24) # AAPL

# Number of minutes for the desired intraday bars
context.intraday_bar = 5

# Update bars every x minutes of trading day
for i in range(1, 390): # Loop through max of 390 minutes in a trading day
if i % context.intraday_bar == 0:
schedule_function(get_intraday_bar, date_rules.every_day(),
time_rules.market_open(minutes=i))

The handle_data function

The Quantopian built-in handle_data function is automatically called every minute of the trading day. We want this function to get the past one-minute bar data for our stock and log the results. This data is saved to the variable ohlcv using the data.current function in line 35. Line 37 logs the past one-minute bar’s open, high, low, close, and volume.

def handle_data(context, data):
"""
Quantopian function called every minute throughout the trading day.
"""
# Print one minute bars for the asset to check calculations
ohlcv = data.current(context.asset,
['open', 'high', 'low', 'close','volume'])
log.info('{}: open={}, high={}, low={}, close={}, volume={}'.format(
context.asset.symbol, ohlcv['open'], ohlcv['high'],
ohlcv['low'], ohlcv['close'], ohlcv['volume']))

Custom get_intraday_bar function

This function is going to get enough past one-minute bars to resample them into the desired 5-minute bars. It gets the past one-minute bars via the data.history function in line 48 and saves them as a pandas DataFrame that I called df.

In the intro, I mentioned that I’ll use the DataFrame resample function to get the custom intraday bars. To do that, I first need to set the rule or the target conversion period. I don’t know why, but they use T to represent minutes. Here’s a list of some of the time series frequencies available. I save the desired rule to the variable resample_period in line 52. Note that the rule must be a string type.

Next, I added a print statement for the resample function in its default form in line 53. If you uncomment this line, you will see the following from the PRINT statements:

DatetimeIndexResampler [freq=<5 * Minutes>, axis=0, closed=left, label=left, convention=start, base=0]

After doing some more digging into how to use this function, I figured out that it can be used with other functions, particularly numpy array functions, including sum, max, min, first, and last. Assume that you have 5 one-minute bars and you want to turn that into a single five-minute bar. The result you want is the following:

  • open = first open value
  • high = max of all high values
  • low = min of all low values
  • close = last close value
  • volume = sum of all volume values

From here, I created another pandas DataFrame object (result) with the resampled intraday bars. Notice in line 55, I initially create the object, then in lines 56-60 I redefine each column of data. The one thing that tripped me up was the default value of that input parameter base is 0. If I left out base, or set it to 0, it ended up printing the last one-minute bar’s values. This didn’t make sense to me, so I just experimented with the values of base. In the documentation, it stated that for a five-minute bin, base could be a value of 0 to 4. So I tried all of these values and noticed how it changed the end result. It didn’t take long to conclude that what I wanted was base=1. This clearly set the “origin” of the intervals to the first one-minute bar.

def get_intraday_bar(context, data):
"""
Function calculates historical ohlcv bars for a custom intraday period.
"""
# Get enough data to form the past 3 intraday bars
bar_count = context.intraday_bar * 3
df = data.history(context.asset, ['open', 'high', 'low', 'close', 'volume'],
bar_count, '1m') # returns a pandas DataFrame

# Resample dataframe for desired intraday bar
resample_period = str(context.intraday_bar)+'T' # T = minute frequency
#print(df.resample(resample_period)) # print to see resample default values

result = df.resample(resample_period, base=1).first()
result['open'] = df['open'].resample(resample_period, base=1).first()
result['high'] = df['high'].resample(resample_period, base=1).max()
result['low'] = df['low'].resample(resample_period, base=1).min()
result['close'] = df['close'].resample(resample_period, base=1).last()
result['volume'] = df['volume'].resample(resample_period, base=1).sum()

# Log the results
log.info('{} {} minute bar: O={}, H={}, L={}, C={}, Vol={}'.format(
context.asset.symbol, context.intraday_bar, result['open'][-1],
result['high'][-1], result['low'][-1], result['close'][-1],
result['volume'][-1]))

Summary

Resampling the one-minute bars into custom intraday bars is an easy task, once you figure out how to properly use the DataFrame.resample function. I found the documentation and examples of how to do this to be cumbersome. That’s why I wanted to share this this with you. If you have other algorithmic trading or Quantopian tasks that you find difficult, please reach out to me (aaron (at) excelintrading.com) and I’ll try to help.

How to Download Historical Price Data Into Excel – Part 3

April 3, 2017 By Aaron Eller

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.

Bonus: Download historical price data from Yahoo or Google for AS MANY stocks as you want.  Yes, send me this free Excel workbook!

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.

Bonus: Download historical price data from Yahoo or Google for AS MANY stocks as you want.  Yes, send me this free Excel workbook!

How to Download Historical Price Data into Excel – Part 2

March 27, 2017 By Aaron Eller

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.

Bonus: Download historical price data from Yahoo or Google for AS MANY stocks as you want. Yes, send me this free Excel workbook!

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) &amp; "+" &amp; Day(start_date) &amp; "+" &amp; Year(start_date)
    
    Dim str_end_date As String:
    str_end_date = MonthName(Month(end_date), True) &amp; "+" &amp; Day(end_date) &amp; "+" &amp; Year(end_date)
    
    Dim google_url As String: google_url = "http://finance.google.com/finance/historical?q=" &amp; ticker &amp; _
        "&amp;startdate=" &amp; str_start_date &amp; _
        "&amp;enddate=" &amp; str_end_date &amp; _
        "&amp;output=csv"

    'Save data from csv file to the active sheet
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" &amp; 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.

Bonus: Download historical price data from Yahoo or Google for AS MANY stocks as you want. Yes, send me this free Excel workbook!

How to Download Historical Price Data into Excel – Part 1

March 20, 2017 By Aaron Eller

You just thought of a new idea for a trading system. You want to test it quickly to see if there is any value in it. The system requires a few technical indicators to be calculated based on past prices.

You are a momentum trader that is screening for the recent top performers in an index or sector.

You are a value investor that is screening for stocks that have had a recent 30% or greater drop in price that may be a good value stock to consider.

You are a long term investor that uses trailing stops on all of your positions. This helps you lock in your winners while giving them room to run. You must update these stops at the end of each trading day or week.

You are creating a trend following system. You are considering which markets that you want to trade in order to give the overall portfolio as much diversification as possible. To do this, you want to calculate the historical correlation between the markets that you are considering.

What do all of these traders have in common? In order to do the tasks that they are working on, they need access to historical price data. The most common type of historical price data is end-of-day (EOD) data for stocks, ETFs, and indices. This is a list of open, high, low, and closing prices. The daily trading volume is also typically included. This is one of the most common data sets that I use as a systematic trader. In this article, I’m going to show you how to download this information for free into Microsoft Excel.

Bonus: Download historical price data from Yahoo or Google for AS MANY stocks as you want.  Yes, send me this free Excel workbook!

Free Sources Available

There are two primary, free sources for this data – Yahoo Finance and Google Finance. We will take advantage of links on both websites that allow users to download this data into a csv file. These links have a specific URL structure that will allow us to automate this process for any stock or date range that we want. In this article, we are going to get this data from Yahoo. So let’s get started!

Get CSV File Download Link Address

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 are going to download three years of daily prices for Apple (Jan 1, 2014 to Dec 31, 2016). To do this, go to www.finance.yahoo.com, and then enter Apple’s ticker symbol AAPL into the Search for news, symbols or companies input box at the very top of the page.

 

This takes us to the main Yahoo Finance page for Apple. Click on the Historical Data tab towards the top 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 time period input and enter the start date (Jan. 1, 2014) and the end date (Dec. 31, 2016). Also notice that you can change the frequency of the prices to be Daily, Weekly, or Monthly prices. For this example, we will get the daily prices.

 

Once the inputs are correct, hit the Apply button. This updates the data listed on the page to only be for the date range and frequency that we entered. Notice just below the Apply button is a link that say’s Download Data. 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 – saving us a lot of time in the future. That is why we need the file download link. To get it, right click on the Download Data link and select Copy address link. If you paste this into a text editor, it should look something like this:

http://chart.finance.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2014&d=11&e=31&f=2016&g=d&ignore=.csv

 

Important note:  If you do not see this Download data 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.

Manually Get External Data into Excel

Now that we have the csv file download address, we can have Excel get this data and put it in the spreadsheet for us. The next few steps will vary slightly depending on the version of Microsoft Excel that you are using. I will describe the steps using Excel 2016.

After opening Excel, we want to make sure that the Developer tab on the ribbon is visible. If this isn’t visible, go to Excel Options, Customize Ribbon, and make sure that the Developer main tab is selected.

 

Now we want to start recording a macro. We will use the macro that we record in the next section to automate this process. To start recording a macro, go to the Developer tab on the ribbon and under the Code section, click on Record Macro.

 

You can also start recording a macro by pressing the record macro button on the bottom left-hand corner of the Excel sheet. Either of these methods brings up a Record Macro prompt where you can enter the macro name, description, and location to store it. We will just use the default settings, so hit OK.

   

 

Now that a macro is recording, it will record the code for everything that we do inside of Excel. Go to the Data tab on the ribbon and under the Get External Data section, click on the From Text button.

 

This brings up a window to import a text file. Under the file name, paste the URL download address that you copied, and then hit Open.

 

Excel will retrieve this csv file and determine that the data is delimited. This means that characters like commas separate each data value. This automatically brings up a Text Import Wizard to help properly put the data into the spreadsheet. Select that the data is Delimited, then hit Next.

 

For step 2 of the import process, select the Comma to be the delimiter. You should see the data preview below properly separate the columns. Hit Next.

 

Step 3 allows you to set the format for each column of data. We will just use the default settings for now, so hit Finish.

 

The import wizard now asks where you want to put the data. We will put it in the existing worksheet in cell $A$1. This is the default setting, so hit OK.

 

Now all of the price data that we wanted for Apple has been put into the spreadsheet. The column headers are Date, Open, High, Low, Close, Volume, and Adj Close. Now stop the macro that was recording in the background. Do this by going to the Developer tab on the ribbon and hit the Stop Recording button under the Code section.

 

You can also hit the stop recording button at the bottom left-hand corner of the Excel sheet.

 

Automatically Get External Data into Excel

If we had to follow the steps outlined so far each time we wanted to get historical price data for a stock, it would be a very slow process. That is why we want to train Excel to do this for us automatically, regardless of the specific data that we want. We are going to create Excel macros to do this for us.

The next step is to look at the code that was recorded for us. To view the code, open the Visual Basic screen. You can do this by going to the Developer tab on the ribbon, and clicking on the Visual Basic icon in the Code section. Or a simple Windows shortcut is ALT + F11.

 

Once the Visual Basic screen opens, it should display the Macro1 code that was just recorded. If not, open the Modules folder in the Project Explorer on the left, and then double click on Module1. You should see code that looks like this:

 

Looking at the code, you can see that with the active sheet, a query table from our text or csv file link address was added to range $A$1. This query table has many settings, but the important ones are:

  • .TextFileParseType = xlDelimited
  • .TextFileCommaDelimiter = True
  • .Refresh BackgroundQuery:=False

These settings tell Excel that the text file imported into the spreadsheet has data that is delimited and separated by commas. The .Refresh method tells Excel to update the data on the sheet. Without this statement, the data would not be saved to the sheet.

This recorded code will be the basis for the automatic data download macro that we will write. But before we get too far into the programming, let’s setup our spreadsheets to make this process easier.

Input Sheet

First, we are going to setup a worksheet where we can enter the details for the data that we want to get from Yahoo. We’ll name this sheet, Inputs. At the bottom of the Excel workbook, right click on a sheet name and select Rename. Enter Inputs as the name.

On this sheet, we will setup specific cells to be the macro inputs. We’ll put the input descriptions in column A and have the user enter the input values into column B. The descriptions are Ticker Symbol, Start Date, End Date, and Frequency. Put these descriptions in rows 4 through 7 in column A.

We want to make the input value cells stand out so that it is clear to the user where to enter this info. Let’s add borders around each input cell and fill the cells with a color to help show that they are user inputs. To add borders and change the cell fill color, first select the cells that you want to do this to. Then go to the Home tab on the ribbon and look under the Font section. The Borders and Fill Color options are beside one another. Click on the down arrows for each and select the borders and color that you want.

 

I like to use outside borders and a light green fill color to show that a cell is an input. Feel free to customize it to your liking. Here’s what my Inputs sheet looks like:

 

Now, we are going to make each of the input value cells a named range.  This will make it easier to read our code and reference the correct cell in the code. This just means that we can use the cell’s name instead of the cell location (for example, ticker vs. B4). Start by clicking on cell B4 where we want to enter the ticker symbol. To create a named range, go to the Formulas tab on the ribbon and click on the Name Manager icon in the Defined Names section. Click New and then enter the desired name for the cell selected. Repeat this for all four inputs. We are going to use the following names:

  • ticker (cell B4)
  • start_date (cell B5)
  • end_date (cell B6)
  • frequency (cell B7)

 

Note that spaces are not allowed for named ranges. That is why we will use an underscore for a space. This is also a preferred way to name programming variables to make them more readable.

Data Sheet

Now we are going to create a worksheet to be the destination for the data download. We will name this sheet, Data. At the bottom of the Excel workbook, click on the New Sheet button. Or you can right click on an existing sheet, and select Insert. To rename the new sheet, right click on the sheet name, and select Rename. Enter Data as the name. That is all we need to do with this sheet for now.

Get Yahoo Historical Prices Sub-Routine

At this point we could make one macro to read the information from the Inputs sheet and download the desired data to the Data sheet. Instead, we will create one macro to read the user inputs and pass this information to another macro to handle the download process. The advantage to separate this and make the downloading process a single sub-routine is that we can take this block of code and easily use it in other programs that we will create later. Let’s create the Yahoo historical prices download macro first. Copy the code below and paste it into the code area of the Visual Basic screen. Make sure to scroll all the way to the right to get all of the code. Here’s the code:

Sub get_yahoo_historical_prices(ticker As String, start_date As Date, end_date As Date, frequency As String)
'sub-routine saves the historical price data for the ticker, dates, and frequency desired
'sub-routine saves the data on the active sheet when called
    
    'Get desired URL structure for Yahoo Finance
    Dim a As Integer: a = Month(start_date) - 1
    Dim b As Integer: b = Day(start_date)
    Dim c As Integer: c = Year(start_date)
    Dim d As Integer: d = Month(end_date) - 1
    Dim e As Integer: e = Day(end_date)
    Dim f As Integer: f = Year(end_date)
    Dim g As String
    If frequency = "Daily" Then
        g = "d"
    ElseIf frequency = "Weekly" Then
        g = "w"
    ElseIf frequency = "Monthly" Then
        g = "m"
    Else
        MsgBox ("Invalid frequency input for " & ticker & "." & vbNewLine & _
            "Frequency input was set to : " & frequency & vbNewLine & _
            "Frequency input must be Daily, Weekly, or Monthly.")
        Exit Sub
    End If
    
    Dim yahoo_url As String: yahoo_url = "http://chart.finance.yahoo.com/table.csv?" & _
        "s=" & ticker & _
        "&a=" & a & _
        "&b=" & b & _
        "&c=" & c & _
        "&d=" & d & _
        "&e=" & e & _
        "&f=" & f & _
        "&g=" & g & _
        "&ignore=.csv"

    'Save data from csv file to the active sheet
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & yahoo_url, Destination:=Range("$A$1"))
        .Name = ticker
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .Refresh BackgroundQuery:=False
    End With

End Sub

 

So let’s walk through this code to better understand what it is doing. First off, the code defines the variables a, b, c, d, e, f, and g. These are used to help get the specific Yahoo download link address based upon the inputs to the sub-routine. Here is the link for the example that we used earlier:

http://chart.finance.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2014&d=11&e=31&f=2016&g=d&ignore=.csv

Let’s break this down to better understand the URL structure:

http://chart.finance.yahoo.com/table.csv?
s=AAPL&
a=0&
b=1&
c=2014&
d=11&
e=31&
f=2016&
g=d&
ignore=.csv

 

Notice that s is set to the ticker symbol for Apple, or AAPL. It’s not obvious, but the letter a is set to the start date’s month minus 1. The letter b is set to the start date’s day, and letter c is set to the start date’s year. Similarly, letter d is set to the end date’s month minus 1, e is set to the end date’s day, and f is set to the end date’s year.

Letter g is set to d. This is because the frequency of the data was set to Daily. If this was set to Weekly, g would be equal to w. If the frequency was Monthly, then g would be equal to m.

The last part of the URL is ignore=.csv. This will be the same, regardless of the data desired.

So, this is exactly what the code is doing. It defines letters a through f based on the start and end date inputs. Then it defines g based on the frequency input. Notice that g must be Daily, Weekly, or Monthly. If this input is not one of these values, then the code will give a message to the user stating that the frequency input wasn’t valid and then exits the sub-routine.

After letters a through g are defined, the code can define what the specific Yahoo download address should be. This is saved to the variable yahoo_url.

Now that the code knows the link to the csv text file download, it uses the same block of code that we recorded earlier to add a query table to the active sheet. This is what actually retrieves the data from Yahoo’s website and saves it on the active sheet. It uses the yahoo_url variable instead of the hard coded address that we copied. It also only has the query table settings that are necessary.

Yahoo 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 yahoo_prices. Copy the code below and paste it into the code area of the Visual Basic screen. Here’s the code:

Sub yahoo_prices()
'sub-routine retrieves the user inputs from the "Inputs" sheet and passes this into the Get_Yahoo_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
 Dim frequency As String: frequency = Sheets("Inputs").Range("frequency").Value
 
 'Activate appropriate sheet to put price data
 ThisWorkbook.Sheets("Data").Activate
 'Delete any data currently on the sheet
 Cells.Delete
 
 'Call the yahoo sub-routine
 Call get_yahoo_historical_prices(ticker, start_date, end_date, frequency)

End Sub

 

So what it is this code doing? First, it defines the four user inputs from the Inputs sheet. It references the named ranges on this sheet that we created earlier. 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_yahoo_historical_prices sub-routine that we previously created, and it passes the four user inputs into the sub. This call statement will run the other sub-routine.

Test It Out!

Let’s test out this new macro. Go to the Inputs sheet and enter the inputs in the appropriate cells if you haven’t already. To make it easier to call our macros, let’s add a button on the “Inputs” sheet that we will link to this sub-routine. That way if we want to run the macro, we can enter all of the inputs and just push this button.

There’s several ways to do this. We will go the Insert tab on the ribbon and click on Shapes under the Illustrations section. Under Rectangles, we will select the Rounded Rectangle. Click on the sheet, and drag the cursor from the top left to the bottom right to create a rectangle. Right-click on this and click on Edit Text. Enter Get Prices From Yahoo. Now right-click on the rectangle again, and click on Assign Macro. Select the yahoo_prices sub that we created. Here’s what my Inputs sheet looks like now:

 

Click on the new macro button. It should run the yahoo_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 into Excel. Enter some different ticker symbols, dates, and data frequency, and test it out.

So to conclude, if you would like to download this Excel workbook for free, you are in luck. You can get it below with several extra features also thrown in, including the ability to automatically download the data for a list of stocks. In the next article, I’ll show you how to download this data from Google Finance.

Bonus: Download historical price data from Yahoo or Google for AS MANY stocks as you want. Yes, send me this free Excel workbook!

Join My Free Newsletter

* = required field

Copyright © 2021 Excel In Trading