Analyzing Historical Price Data for Multiple Assets in Excel

Click to Download Workbook: Historical Stock Data

This workbook downloads historical stock data for multiple assets and analyzes the correlation (or covariance) with either prices or returns. The correlation will be displayed in a matrix format using the Analysis ToolPak. Correlation and Covariance are measures of how closely two or more assets move together over a predefined time range. Correlation is essentially a normalized version of covariance with a value of -1 to 1. Excel is a tremendous pain to deal with in terms of obtaining data. It’s slow, and cumbersome to account for many assets. But sometimes, it is necessary. We can obtain historical stock quotes through a vendor’s API. This could be paid or free. Other services such as Bloomberg, Market XLS, or Money.Net provide data feeds within Excel as an add-in for a subscription fee. We can utilize Google Finance’s historical data API in order to gather the data we need for free. This price data can be used for a multitude of models within Excel. Disclaimer, the Yahoo historical price data API is defunct. This means we cannot pull data from Yahoo Finance in Excel anymore. However, the quantmod module within R has patched this and still supports requesting data from the Yahoo API.

This is really quite a simple routine we can implement within VBA. All we need to do is iterate over a range of tickers and query the data for each from a text file and delimit it within VBA. This is crucial because this module only imports closing prices. This will ensure our program runs faster due to the fact that we are importing less data into excel. We could modify it to allow us to import the open, high, low, and volume columns, and collate each, but that is outside the scope of this post. Here is an excerpt of the workbook code which is the heart of how it works.

Note error handling is also accounted for in this subroutine. This means if an invalid ticker is entered, we will place it in a designated column on the worksheet. We could also use an array for this. The next step is to calculate the daily return for each stock. We will do this by dynamically autofilling data. Admittedly, this is an ad-hoc way of dealing with performing calculations on a data set. However, it will work for this scenario. We will add a new sheet and fill everything we need to on it.

The last step is to actually analyze the data. This can be done by automating the Analysis ToolPak Correlation and Covariance applications. We will do this by passing in a variable range which will adjust to different time frames. We can also use an if statement to determine if the user selected either a covariance or correlation analysis. Either way,
the code will be very similar.

 

To run the workbook, make sure you choose a time frame and select either a covariance or correlation analysis type. From there, select the analysis to be on historical returns or prices. The output consists of basic statistics for each stock on the main sheet and a collated sequence of price data and returns on separate sheets. The analysis is also on a separate sheet. I choose the following assets and parameters:

This was the output. Note that correlation matrix is conditionally formatted with the following parameters.

Red: High Correlation (Value .75 to 1)

Yellow: Medium Correlation (Value  0 to .7499)

Green: Little Correlation (Value -1 to -0.01)

Note that all of the code isn’t posted on this page, just the most important aspects. Simply view it in the workbook to see the code in its entirety.

About the author

programmingforfinance

Hi, I'm Frank. I have a passion for coding and extend it primarily within the realm of Finance.

View all posts

2 Comments

  • Great Work. Congratualations !
    Is it possible to get quotes for foreign stocks, currencies and commodities?
    I´m not sure what the ticker symbol structure for those are and incorrect ticker symbols (with a “:” or “-“) resulted in error messages. Thanks.

    • To my knowledge, you can’t. The workbook uses Google Finance to retrieve historical data. For foreign stocks, you’re better off finding an ADR (American Depository Receipt) that tracks the underlying asset. For example, Adidas trades in Germany on the Frankfurt exchange but has an ADR (ADDYY) that has data on the Google Finance servers. As for commodities and currencies, I don’t think Google Finance supports these in terms of gathering historical data. Even Yahoo only supported some commodities and currencies.

Leave a Reply

Your email address will not be published. Required fields are marked *