Seeking Alpha

MattEdgarCFACPA's  Instablog

MattEdgarCFACPA
Send Message
I am the principal at Sawtooth Capital Management, LLC, which is a registered investment advisor in the great state of Texas. After graduating with my MBA I went to work in the oil and gas business as a financial analyst and business manager. I've also started and sold several businesses. I'm a... More
My company:
Sawtooth Capital Management, LLC
  • Pulling Historical Stock Data Into A Google Spreadsheet (It's Easy!!) 21 comments
    Feb 19, 2013 12:55 PM | about stocks: C, GOOG

    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).

    Ticker example

    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.

    (click to enlarge)GoogleFinance Formula

    Here's what the formula in A2 will look like:

    GoogleFinance formula

    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).

    GoogleFinance price formula

    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.

    Disclosure: I am long C, GOOG.

    Additional disclosure: Many of my clients have long positions in both Google and Citigroup Inc.

Back To MattEdgarCFACPA's Instablog HomePage »

Instablogs are blogs which are instantly set up and networked within the Seeking Alpha community. Instablog posts are not selected, edited or screened by Seeking Alpha editors, in contrast to contributors' articles.

Comments (21)
Track new comments
  • Snefsky
    , contributor
    Comment (1) | Send Message
     
    Great post, Matt! This will be quite helpful for gathering historical data for left and right tail analysis when calculating the probability of profit on different options positions. I typically use the historical data on Yahoo Finance and export it to spreadsheet format. But, I like the look of this approach much better. This will be a time saver!
    20 Feb 2013, 01:28 AM Reply Like
  • MattEdgarCFACPA
    , contributor
    Comments (11) | Send Message
     
    Author’s reply » Happy to help. Google Spreadsheet has some limitations versus Excel, but it seems to get a little better each month.
    22 Feb 2013, 07:01 AM Reply Like
  • Just89
    , contributor
    Comment (1) | Send Message
     
    I appreciate this post a lot as I am currently learning how to use google spreadsheets.

     

    Assuming you still check the comments...

     

    Do you know how I can get historical quotes throughout a day? Say 15 minute intervals for a whole day?

     

    Thanks!
    27 Jun 2013, 01:36 AM Reply Like
  • MattEdgarCFACPA
    , contributor
    Comments (11) | Send Message
     
    Author’s reply » As of right now I don't believe it is possible to pull intraday data. However, Google is always adding capabilities to its spreadsheet, so this may (if it hasn't already) change.
    24 Jul 2013, 03:27 PM Reply Like
  • don'tbugmeplease
    , contributor
    Comments (184) | Send Message
     
    Currently when I use "all" as the attribute in the GoogleFinance call,
    I get blank, with an error: "The 'all' data is invalid."

     

    This doesn't seem to work anymore.
    24 Jul 2013, 03:48 AM Reply Like
  • MattEdgarCFACPA
    , contributor
    Comments (11) | Send Message
     
    Author’s reply » You may want to double check your formula. The formula in the original post still works fine for me. I was able to recreate the error message you received by using the following formula: =GoogleFinance( A1 ; "'all'" ; WORKDAY( TODAY(); -21 ) ; TODAY() ). A1 refers to the cell which contains the ticker symbol.

     

    NB: The word ALL should be enclosed in double quotes only. In this example I enclosed ALL in single quotes and then again in double quotes.

     

    The following formula works fine: =GoogleFinance( A1 ; "all" ; WORKDAY( TODAY(); -21 ) ; TODAY() )
    24 Jul 2013, 03:27 PM Reply Like
  • bjkaufman
    , contributor
    Comments (15) | Send Message
     
    Is there any way of importing a company's financials from a balance dheet into the spreadsheet, i.e.
    Current assets
    Current liabilities
    Total assets
    Retained earnings
    EBIT
    Total liabilities
    Market capitalization
    Revenues
    This would also help in analyzing a company?
    1 Nov 2013, 11:29 AM Reply Like
  • MattEdgarCFACPA
    , contributor
    Comments (11) | Send Message
     
    Author’s reply » As of right now the function only pulls price related data, but as with all things tech, I expect the GoogleFinance function to improve and offer more in the future.
    8 Nov 2013, 09:10 AM Reply Like
  • anesthesiaman
    , contributor
    Comments (24) | Send Message
     
    Yes there is a easy way. Google the importHTML function and you can pull a company's financials from the Key Statistics section (or any other section) in Yahoo Finance or your favorite website - directly into google spreadsheet.

     

    I love it. An elegant solution.

     

    I love google spreadsheets. It has been a huge analytical timesaver for me.
    23 Nov 2013, 10:56 PM Reply Like
  • Mike Shrum
    , contributor
    Comments (2) | Send Message
     
    Not sure I understand how to formulate that. Could you give an example formula of how to pull up info on say, Cash from Operating Activities, or maybe EPS Growth Rate. Please and thank you.I did a search but not sure exactly what to search fo
    27 Apr 2014, 10:48 PM Reply Like
  • gkotak
    , contributor
    Comments (4) | Send Message
     
    Hi, I was wondering if you could help me. I want to important the 12-month ending revenue at let's say - http://bit.ly/1wK3N8s. The importHTML gets the entire table. Is there an easy way to parse out just the revenue number?

     

    Thanks,
    Gaurav
    5 Mar, 05:08 PM Reply Like
  • Bradley Sugarman
    , contributor
    Comments (33) | Send Message
     
    Excel article! Google Docs is such a powerful, free device.
    7 Nov 2013, 08:11 PM Reply Like
  • Michael K. Dawson
    , contributor
    Comments (20) | Send Message
     
    Great article. I'm using the following formula

     

    =googlefinance(B1,"clo...

     

    It is only returning 19 days of data. Is there something funky going on due to the holidays? Also it looks like the final data point (today) is not updated until after Midnight. Is that the way it works? Would be nice to have an updated 20DMA after the close.

     

    Thanks
    17 Jan 2014, 08:06 AM Reply Like
  • Michael K. Dawson
    , contributor
    Comments (20) | Send Message
     
    looks like it chopped off my formula:

     

    "googlefinance(B1,"clo...
    17 Jan 2014, 08:08 AM Reply Like
  • Michael K. Dawson
    , contributor
    Comments (20) | Send Message
     
    It continues to chop off my formula, but it is the same as yours.
    17 Jan 2014, 08:11 AM Reply Like
  • rajiv1
    , contributor
    Comments (3) | Send Message
     
    Excellent article. Hopefully you are still checking the comments. Any idea on how to import the same attributes for weekly and mopnthly data?

     

    Thanks.
    23 Feb 2014, 05:19 PM Reply Like
  • Mike Shrum
    , contributor
    Comments (2) | Send Message
     
    Great Post, Is it possible to pull information such as Return on Capital (5 year,avg), and or book value per year, Income statements per year,eps ,etc. Much like you did for the price history
    27 Apr 2014, 10:48 PM Reply Like
  • KRBabu
    , contributor
    Comments (15) | Send Message
     
    Matt: Do you have a similar solution to extract data from Yahoo? Yahoo provides 'Adj Close' prices that I find valuable in some analyses. Thanks.
    8 Jul 2014, 11:54 AM Reply Like
  • svy
    , contributor
    Comments (57) | Send Message
     
    =ImportHtml("http://yhoo.it/1qh96qx","table",0)

     

    krbabu this code should get the 'adj close' into google sheets as part of 7 column wide import...the 7th column would be 'adj close'...
    4 Sep 2014, 02:05 PM Reply Like
  • svy
    , contributor
    Comments (57) | Send Message
     
    http://seekingalpha.co...

     

    http://seekingalpha.co...

     

    scott's investments is sharing google sheets with what appear to be links both finviz & yahoo...

     

    examples of coding:

     

    =ArrayFormula( TRIM( SUBSTITUTE( SUBSTITUTE( SPLIT( CONCATENATE( REPT( ImportHTML("http://bit.ly/1t4tPw3?t="&E26,"table",13) & CHAR( 9 ) ; ISNUMBER( MATCH( ( {1,2,3,4,5,6,7,8,9,10,... + ( 12 * {0;1;2;3;4;5;6;7;8;9;1... ) ) ; { 36;48;60;140 } ; 0 ) ) ) ) ; CHAR( 9 ) ) ; "*" ; "" ) ; "%" ; "" ) ) + 0 )

     

    =IF(VWO!H2< INDEX( FILTER( VWO!H4:H ; ISNUMBER(VWO!H4:H) ) ;1;1) ; "Invested" ; "Cash" )

     

    I don't know if the 2nd coding example is pulling from yahoo...
    4 Sep 2014, 01:08 PM Reply Like
  • User 340220
    , contributor
    Comments (8) | Send Message
     
    Morning!!

     

    I'd like to pull the Future 5 years EPS or Normalized EPS for an Auto-Populating Graham Spreadsheet. Any idea how to get EPS for the NEXT 5 years?

     

    I see articles with 2019 Expected EPS Growth - and CANNOT find a way to pull them from any online sources??
    9 Jan, 10:24 AM Reply Like
Full index of posts »
Latest Followers

Latest Comments


Posts by Themes
Instablogs are Seeking Alpha's free blogging platform customized for finance, with instant set up and exposure to millions of readers interested in the financial markets. Publish your own instablog in minutes.