Looking for historical stock data to use in your spreadsheets can be a frustrating endeavor, to say the least. A quick Google (NASDAQ:GOOG) search uncovers the wide array of work-arounds and frustrations. Many techniques require copying and pasting data and possibly heavy reformatting. Trying to import this data into spreadsheets with existing formulas can also prove hazardous. In addition, in order to keep the historical data fresh you must do these tedious processes on a regular basis.
For those looking to build a spreadsheet that contains historical stock data that works well with formulas and updates automatically, take a look at Google Drive (formerly known as Google Docs). If you haven't used Google Drive in a while, or if you've never used it, it's worth a look. The GoogleFinance functions can alleviate some headaches associated with your quest for historical data.
Take for instance the following situation. I want to create a spreadsheet for Citigroup Inc (NYSE:C) that displays the last 20 days of data, including: opening, high, low, closing price and volume. I then want to use the closing prices to create a 20 day simple moving average. Finally, I want to calculate the percent the current price is of this 20 day moving average.
The very first step is to sign up for a Google account. Assuming you've done this, navigate to Google Drive and click on the 'Create' button and select 'Spreadsheet.' In cell A1 I am going to type the label "Ticker" and in cell B1 I am going to type a "C" (for Citigroup).
In cell A2 I am going to type my GoogleFinance formula, which will populate the cells directly below and to the right with the requested data. The format for the GoogleFinance formula is =GoogleFinance("symbol","attribute","start_date","num_days|end_date","interval"). More information on the formula and attributes can be found here.
Here's what the formula in A2 will look like:
B1 references the ticker symbol in cell B1.
"all" is the attribute that will populate all available data points (open, close, high, low, volume).
The third part of the formula, [workday(today,-21] , looks a little complex, but the embedded formula is simply telling the overall formula to go back and get the data starting 21 periods back [today-21] , but only count back workdays [workday]. If the [workday] formula was not used to wrap the [today-21] formula the data would contain less than 20 days since the formula would count weekends. This operator could also contain a hard coded date or a reference to a date.
The end date, which in this formula is today's date [today] , is optional, but note that you will only get one data point if you don't specify a date range by using the start date and end date (or number of days).
Now we have a nice table of data that will update daily with the past 20 days of historical data.
The hard part is over. Now in cell A24 I will type the text 'Current Price'. In cell B24 I will type the following formula: =GoogleFinance(B1,"price"). This formula looks up the ticker from cell B1 and returns the current price (note, these prices are delayed).
In cell A25 I type the text "Moving Average". In cell B25 I type the following formula: =Average(C3:C22). This formula will average the previous 20 days' closing prices (note, this will update every day).
In cell A26 I type the text "Current as % of MA". In cell B26 I type the following formula: =B24/B25.
The benefit to using Google Drive is the automatic updating of data and the preservation of data formats and formula integrity.
While the formulas look a little different than Excel, anyone with reasonable spreadsheet skills should be able to work with Google's spreadsheets in no time.
Additional disclosure: Many of my clients have long positions in both Google and Citigroup Inc.