For this tutorial we will be using Google Sheets. Google Sheets is a great resource for scraping data from the internet and it’s free!
First, we need to set up our Google Sheet in a format that will make pulling the data easy and accessible for future analysis. In the first row, add in the number of historical work days you’d like to go back into cell A1. We chose to go back 470 days which is just under two years of data. Then, on every next column, add in the Tickers for the stocks/ETFs that you’d like to pull data for. Keep in mind that the number of days back must be less than the lifetime of each your stocks have been on the market. Otherwise, the dates will not correspond with the stock prices for certain newer stocks. By lifetime of a stock I mean the difference between today’s date and the date that the stock had an IPO (went into the market).
Next, assuming that your stocks are populated in cell B1 onwards like the image above, enter the following formula into the respective second row of each column:
=index(GOOGLEFINANCE(B1,"close",WORKDAY(today(),-$A$1),today()),,2)
You can easily just enter this formula into cell B2 and then drag it across row 2 to update the first parameter in the GOOGLEFINANCE function. You can find the GOOGLEFINANCE function’s documentation here. In our snippet of code, we are entering the stock ticker, requesting the close price, giving the start date, and the end date. It will then return a list of all the historical price for that asset.
To get the historical stock date, paste in the following formula into cell A2:
=index(GOOGLEFINANCE($B$1,"close",WORKDAY(today(),-$A$1),today()),,1)
Since we determined that the lifetime of each stock is greater than the number of days back, you only have to have one date. If you would like a date associated with each stock, you can insert a new column between each stock and change the formula to get the date for each respective stock. You can then copy and paste the static values into a separate sheet and use the data for historical analysis.
