Welcome Stockaholics!

We are a new and fast growing financial forum! Sign up for free and let's talk stocks!

  1. Do you want to help develop this community? We are looking for contributions from investors and traders like you! What stocks do you follow? What is hot right now? Sign up and get in on the ground floor of the newest, fastest growing financial forum!
    Dismiss Notice
  2. You will notice a live chat widget on the right. Click in to join us and lets hear about how you nailed that last UWTI trade!
    Dismiss Notice

Google Sheets live and historical prices

Discussion in 'Ask any question!' started by bigbear0083, Feb 3, 2018.

  1. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    It's rare that I ever need to post a new thread in here to ask questions lol.

    This is in regards to Google Sheets and the live price data.

    So, I have been hearing some rumors that Google Finance may be doing away with live and historical price functions in Google Sheets in the near future? I honestly don't know how true this is, but I have noticed some things have been taken away such as the historical price pages. This is no longer accessible (hasn't been for months now actually).

    [​IMG]

    This could pose a bit of an issue for me as I run the Stockaholics competitions through Google Sheets and use the GoogleFinance functions to pull the live and historical prices into the spreadsheets.

    So, my question here is if anyone knows of any other feeds aside from GoogleFinance that I could use to pull live and historical prices into Google Sheets?

    I managed to stumble across this function the other day and it works great (this is just for live prices not historical)-

    Any other one's you guys might have tested out and working for you in Google Sheets?

    This is simply a "just in case". Not that I need it now.

    Everything is working as it should for now, but I'm not sure how long until Google ultimately pulls the plug on this.

    Of course if they offer some paid service to get this data I would just simply subscribe to such service. But, again I don't know.

    Anyway, big thanks to anyone who can help! :)

    Just want to make sure I have an alternative feed in case of the inevitable.
     
  2. JerryM

    JerryM Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    705
    Likes Received:
    364
    Try this command in a google sheet. It pulls in 60 days of price and volume. Not sure if this will help or not

    =IMPORTHTML("http://finance.yahoo.com/q/hp?s="&Summary!$A$1&"+Historical+Prices","table")

    replace Summary!$A1&" with the stock symbol or our own cell number from your sheet that contains the stock symbol.
     
    BermudianOption likes this.
  3. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    Thx for chiming in here @JerryM

    That function you posted above works precisely as you stated. It pulls all of the historical data for the past 60 days. No problemo there.

    However, unfortunately that isn't exactly what I was aiming for here.

    What I basically need here is the formula to pull just the price alone (and preferably the current price, not the closing price).

    Here is a test sheet I just whipped up this morning. Under where it says "PRICE" that's what I'm looking for. Just price alone.

    I also just tested out @Rishi_at_Tiingo's Tiingo API in Google Sheets and that works as it should, but like @JerryM's example it also pulls extra data which I don't need in this case.

    [​IMG]

    I'll keep looking around to see if there is a function that would only pull the prices...
     
  4. JerryM

    JerryM Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    705
    Likes Received:
    364
    Where you are confusing me is that I thought you were looking for "historical" prices. Would that not be the "close" for that day? So here is something that might have what you want but I warn you there is a learning curve to this one that I have not fully mastered yet. Here is the opening to the rabbit hole ;) https://finbox.io/blog/using-the-google-sheets-add-on/

    Another very big rabbit hole would be to move to Excel and use the SMF add-in
    http://ogres-crypt.com/SMF/
     
    bigbear0083 and BermudianOption like this.
  5. BermudianOption

    BermudianOption Active Member

    Joined:
    Apr 6, 2016
    Messages:
    357
    Likes Received:
    244
    I haven't implemented these approaches personally but I did find them a while ago when researching ways to streamline my DD process:

    http://investexcel.net/download-finviz-data/

    Buying a data subscription is a good back up plan. I also want to point out that a lot of advanced platforms all you to grab data via the API without additional charges as well FYI:
    https://www.lockeinyoursuccess.com/connecting-thinkorswim-to-excel-part-1-of-4/
    https://www.lockeinyoursuccess.com/connecting-thinkorswim-to-excel-part-2-of-4/
    https://www.lockeinyoursuccess.com/connecting-thinkorswim-to-excel-part-3-of-4/
    https://www.lockeinyoursuccess.com/connecting-thinkorswim-to-excel-part-4-of-4/
     
    bigbear0083 likes this.
  6. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    @JerryM
    @BermudianOption

    Awesome stuff fellas, thanks!

    @JerryM - I just tested out that finbox add-on and it looks to be working exactly how I want it in Google Sheets.

    I'm going to play around with this some more once I get back from the Super Bowl party later today.
     
    BermudianOption and JerryM like this.
  7. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    For those who don't want to go the route of that finbox add-on (since that one requires registering an account to activate), I found another alternative that pulls live prices from Finviz Elite without adding any add-on's into Google Sheets.

    Here is the formula:

    Where $A$2 is the cell of your stock symbol.

    I haven't tested this in regular trading hours yet so I'm not really sure how much of a delay there is. I will check back in here on Monday.

    Also this formula here pulls some extra data:

    Again, where $A$1 would be the cell of your symbol.

    Not a bad workaround in the event I'm needing to use something other than GoogleFinance functions.
     
  8. Three Eyes

    Three Eyes 2018 Stockaholics Contest Winner

    Joined:
    Dec 14, 2017
    Messages:
    565
    Likes Received:
    498
    I'm hoping the "GoogleFinance" function stays the same. Even though the "redesigned" Google Finance web page has dropped a lot (most!) of the functionality of the old, I think Google is maintaining all the historical price data to show on their current (dumbed-down) charts. In other words, The Google Finance API remains intact and all the "=GoogleFinance" functionality should remain.

    But many Many MANY thanks for starting this thread, because if that functionality IS ever lost, I'll be coming right back to this thread to hurriedly implement the workaround!
     
    bigbear0083 likes this.
  9. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    Shoot, so is it just me or are any of you also not getting prices to pull up in Google Sheets starting from 5/1?

    I'm returning a #REF! when trying to pull prices for 5/1 and 5/2 ... anyone else can confirm this??

    This will be a bit of an issue for me with the stock competitions that I run here as I pull daily and weekly prices in the sheets.

    The "live" price function still works fine thankfully at least, but not the historical prices.

    This will suck big time if they did in fact do away with the historical prices for good here. I was afraid this day might finally come through. =/
     
  10. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    Actually kind of surprised no one had reported this yet since this began on May 1st, as you can see it stops pulling prices after 4/30. Darn.

    [​IMG]
     
  11. Three Eyes

    Three Eyes 2018 Stockaholics Contest Winner

    Joined:
    Dec 14, 2017
    Messages:
    565
    Likes Received:
    498
    Sorry, I just now saw this message. I don't use historical price data in Google Sheets, but I went ahead and tested your formula. As seen below, something was up for a couple days...It's not parsing Tuesday's and Wednesday's data.

    Screenshot 2018-05-04 at 12.26.48 AM.png

    Here's what I got when I tried for just May 2:

    Screenshot 2018-05-04 at 12.32.18 AM.png
     

    Attached Files:

    bigbear0083 likes this.
  12. Three Eyes

    Three Eyes 2018 Stockaholics Contest Winner

    Joined:
    Dec 14, 2017
    Messages:
    565
    Likes Received:
    498
    Doesn't look like you can fool it into coughing up the data either...

    Screenshot 2018-05-04 at 12.45.25 AM.png
     
    bigbear0083 likes this.
  13. Three Eyes

    Three Eyes 2018 Stockaholics Contest Winner

    Joined:
    Dec 14, 2017
    Messages:
    565
    Likes Received:
    498
    Although, aha! "closeyest" gave me the correct May 2 data. Makes me think the May 1 and 2 data are still bouncing about in their database somewhere...

    Screenshot 2018-05-04 at 12.50.35 AM.png
     
    bigbear0083 likes this.
  14. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    Oh wow thanks for that heads up @Three Eyes!

    I wasn't even aware that they added yesterday's data.That is at least somewhat encouraging to see. I could live with May 1st and 2nd gone missing as long as it resumes adding data again which it looks like it might be. We'll have to see if today's data gets added after the market close. Keeping my fingers crossed here. :p

    Meanwhile, I'll just manually input the missing data in the spreadsheets for May 1st, no biggie there. Hopefully no more days go missing! Lol.
     
    Three Eyes likes this.
  15. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    Looks like this has indeed resumed adding data again, but still kind of strange that those 2 missing days (5/1 and 5/2) wouldn't be added in there. I wonder if this will ever be corrected in the future. No worries though as long as it was just those 2 days and nothing more from here on out. We shall see.

    [​IMG]
     
  16. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    whelp, as i had feared, it looks like GF has scrapped this formula-

    PHP:
    =GOOGLEFINANCE("AAPL""price"DATE(2018,4,1), DATE(2018,5,31), "DAILY")
    here is what i get in google sheets-

    [​IMG]

    could someone also confirm this for me? i'm wondering if others are also seeing the same thing.

    however, it looks like this formula is still working for me (for now... :rolleyes:)-

    PHP:
    =GOOGLEFINANCE("AAPL","price",TODAY()-30,TODAY())
    [​IMG]

    which has me wondering if GF is getting ready to pull all historical data from its database ... 5/1 and 5/2 prices are still missing.

    any of you guys know where i could find some kind of reliable source for historical prices? i don't mind if there are fees tbh.

    it will be rather difficult to run the competitions here on the forums via google sheets if the historical prices are getting pulled.

    will be a sad day when that happens ... which honestly could be any day now. :confused:
     
  17. anotherdevilsadvocate

    anotherdevilsadvocate Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    3,146
    Likes Received:
    1,751
    Hey Cy it looks like your second formula is doing the same thing that the first formula is supposed to do? Maybe they just updated the function to not take the fifth parameter "daily". i.e. you should be able to get by unless you are trying to get weekly/monthly/non-daily info.

    As for the problems with the first 2 days of this month, in tradingview there's also been a problem with the volume data on many (but not all) stocks. I wonder if there's some problem going on under the hood like back when there was a data hack about 1-2 years ago.
     
    bigbear0083 likes this.
  18. Three Eyes

    Three Eyes 2018 Stockaholics Contest Winner

    Joined:
    Dec 14, 2017
    Messages:
    565
    Likes Received:
    498
    Seems to be working on my end... [altho, as you noted, still missing those two first days in May.]

    Screenshot 2018-05-11 at 12.51.19 PM.png
     
    bigbear0083 likes this.
  19. bigbear0083

    bigbear0083 Content Manager
    Staff Member

    Joined:
    Mar 29, 2016
    Messages:
    15,592
    Likes Received:
    4,943
    hate to keep bumping this thread for what seems like every other week, but i just wanted to quickly confirm with others out there if anyone is experiencing missing historical data for some tickers from this past Friday (5/18)?

    i say "some tickers" because not all of them are missing like most of the times when this occurs... first time i think i have experienced this kind of glitch o_O

    so for example something like AAPL has missing historical data for 5/18-
    [​IMG]

    whilst something like CHK has it pulling up just fine-
    [​IMG]

    you guys also experiencing this issue?

    oh and btw, looks like data from 5/1 and 5/2 has been restored which was pretty cool to see.
     
  20. Three Eyes

    Three Eyes 2018 Stockaholics Contest Winner

    Joined:
    Dec 14, 2017
    Messages:
    565
    Likes Received:
    498
    [getting down to my last hours here in USA, but time enough to check for ya. ;)]

    Same results as you:

    AAPL historical 2018-05-21 at 11.02.19 AM.png
     
    bigbear0083 likes this.

Share This Page