=googlefinance() working in downloaded spreadsheet?

Discussion in 'Ask any question!' started by anotherdevilsadvocate, Oct 3, 2016.

  1. anotherdevilsadvocate

    anotherdevilsadvocate Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    4,319
    Likes Received:
    3,375
    I was on vacation for two weeks and need to update my historical data, figured I would download a spreadsheet for the last ~5 years of daily data on a stock.

    So in the past I've used Google Spreadsheet
    =googlefinance($A$1,"all",date(2010,1,1),date(2020,12,31),"daily")
    would be in cell A2

    Currently, it looks good in Google Spreadsheet but then when I try to
    File,
    Download as,
    Microsoft Excel (.xlsx)

    I get an error; the downloaded spreadsheet shows this in A2:
    =IFERROR(__xludf.DUMMYFUNCTION("googlefinance($A$1,""all"",date(2010,1,1),date(2020,12,31),""daily"")"),"#N/A")

    I have Excel 2010 in Windows 7.

    @Cy McCaffrey has helped me with Google Spreadsheet before.
     
    Stockaholic likes this.
  2. anotherdevilsadvocate

    anotherdevilsadvocate Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    4,319
    Likes Received:
    3,375
    I am open to other ways of getting the historical data, for ~400 tickers.
    I currently have 20 Google Spreadsheets, each with 20 tabs.

    That is not so fast, but once I have the historical data then I have an Excel macro that gets the current day's data.

    So, kinda hate that I made this thread title so specific...if someone knows a way to do this other than Google Spreadsheets then that'll fly too.
     
  3. Stockaholic

    Stockaholic Content Manager

    Joined:
    Mar 29, 2016
    Messages:
    13,767
    Likes Received:
    7,050
    First and foremost a big time ..... and I do mean a BIG TIME welcome back to you Marcy. I gotta be absolutely honest here but checking up on the boards this early AM to see your name on the list of newest posts has admittedly got me pretty pumped up here haha. Hope you had a really awesome trip. I can't stress this enough but you've been missed around here in a huge way.

    As for the spreadsheet troubleshoot I'll need to come back to this thread a little later today as I'm literally on mobile now. At first blush I don't believe this is a very difficult issue to resolve. But kind of hard to tell without being on my desktop. I'll try to remember to check back to this thread later today or this week.

    Thx for the heads up on your return from vaca. Right in time for the new month and quarter and election just around the corner. Nice!
     
  4. Gray Wolf

    Gray Wolf Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    728
    Likes Received:
    398
    Correct me if I am wrong Cy but I don't think the google finance commands work in Excel.
     
    Stockaholic likes this.
  5. Stockaholic

    Stockaholic Content Manager

    Joined:
    Mar 29, 2016
    Messages:
    13,767
    Likes Received:
    7,050
    @JerryM ah yes i believe you are correct there ... my apologies! admittedly i was half asleep at the time i had posted haha

    that said, i haven't actually tested that particular formula on excel though, but i'm assuming you are right on that it doesn't function the same as on google sheets ... unless someone can confirm this ... i'll give it a whirl myself later today just to be 100% certain
     
  6. anotherdevilsadvocate

    anotherdevilsadvocate Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    4,319
    Likes Received:
    3,375
    The googlefinance() function doesn't work, but there was some way of downloading the values themselves?
     
  7. anotherdevilsadvocate

    anotherdevilsadvocate Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    4,319
    Likes Received:
    3,375
    I could download before. But searching today I find this

    https://support.google.com/docs/answer/3093281?hl=en
    • Historical data cannot be downloaded or accessed via the Sheets API or Apps Script. If you attempt to do so, you will see a #N/A error in place of the values in the corresponding cells of your spreadsheet.
    Maybe I have to try make it not look like historical data...
     
  8. Stockaholic

    Stockaholic Content Manager

    Joined:
    Mar 29, 2016
    Messages:
    13,767
    Likes Received:
    7,050
    hmm ... well i'm still kind of stumped on this one lol ... but if/when i do find something workable i'll be sure to pass it along marcy!
     
  9. Gray Wolf

    Gray Wolf Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    728
    Likes Received:
    398
    On this stock summary sheet I made up https://docs.google.com/spreadsheets/d/1zxaY2rFe2RpWqG_-8_LCbKiIYCvDxUckHjeyCFZRlJQ/edit?usp=sharing the price graph comes from pulling historical data from Yahoo Finance. If you look on the price history tab in cell A2 you will see the command I use to pull the data. I got that from the following link https://www.quora.com/How-do-you-import-fundamental-stock-data-from-Yahoo-into-Google-Sheets It looks like it is pulling about 3 months but I have not played with the command to see if I can pull more history then what the command is currently pulling.
     
    Stockaholic likes this.
  10. Stockaholic

    Stockaholic Content Manager

    Joined:
    Mar 29, 2016
    Messages:
    13,767
    Likes Received:
    7,050
    @JerryM cool! i never actually used yahoo finance to pull data into google sheets before ... interesting and i wonder now if this could be downloaded (not the spreadsheet but the values) into excel and if it works? haven't tested it myself yet
     
  11. anotherdevilsadvocate

    anotherdevilsadvocate Well-Known Member

    Joined:
    Apr 3, 2016
    Messages:
    4,319
    Likes Received:
    3,375
    Guys thank you so much. I didn't expect you to look into this so deeply, I was just hoping to catch the attention of someone who already knew about the problem beforehand. You guys have done so much, and I really appreciate it.

    As for my situation, I went and copy-pasted (it was just ~400 tickers...I guess lol). I could do that on a weekend. But my solution is not ready for the masses.
     
  12. TomB16

    TomB16 Well-Known Member

    Joined:
    Jun 22, 2018
    Messages:
    4,270
    Likes Received:
    2,625
    Googlefinance stopped working on Google Sheets, last night. Has anyone else noticed this? It appears there is no automated stock data at all.

    Is this a new policy or is it just temporarily broken? Google's web site still talks about googlefinance() on Google Sheets so it appears to be a glitch?
     
  13. removedatuserrequest

    removedatuserrequest Well-Known Member

    Joined:
    Feb 8, 2021
    Messages:
    998
    Likes Received:
    1,290
    Phew! Glad to see I'm not the only one! I was wondering about this too, cause I run a monthly stock picking game via Google Sheets using the Google Finance formulas and all I've been seeing for the past few days is this. Very sad to see. :(

    I really hope this isn't what I've been fearing for a long time now (the end of google finance in google sheets?).

    2.png
     
    T0rm3nted and TomB16 like this.
  14. TomB16

    TomB16 Well-Known Member

    Joined:
    Jun 22, 2018
    Messages:
    4,270
    Likes Received:
    2,625
    Thank you for confirming what I am seeing, BMC.

    There are a few alternatives.

    AlphaVantage - Requires sign up and free token. Will return JSON result. I am just starting to look at this.

    YahooFinance - A few people claim this is still working with the WEBSERVICE() function. I have not tested it.

    IEX - Requires sign up and free token. Will return JSON result. I have successfully downloaded information but have not been able to get PARSEJSON to work but I didn't spend much time, when I was experimenting with it. Instead, I switched to Google Sheets and the effortless GOOGLEFINANCE() call.

    I do have some C++ code that parses IEX JSON results. It wouldn't take much to dust this off and it can already create .CSV files. I'm no longer prepared to invest the energy in creating a database and my own API. My investing approach is so slow and calculated that it would not make sense to create this.


    I doubt there are any KDE users here but I like YapStocks. It works great and integrates quotes nicely into the Plasma desktop. Please keep in mind, I'm not a trader so I am not suggesting YapStocks is good for technical analysis, as I wouldn't know.
     
    removedatuserrequest likes this.
  15. TomB16

    TomB16 Well-Known Member

    Joined:
    Jun 22, 2018
    Messages:
    4,270
    Likes Received:
    2,625
    Hey BMC, it's back!

    Game on, my friend. :thumbsup::)
     
    removedatuserrequest likes this.
  16. removedatuserrequest

    removedatuserrequest Well-Known Member

    Joined:
    Feb 8, 2021
    Messages:
    998
    Likes Received:
    1,290
    omg yes!! was actually really worried about this all week. i didn't know if this was going the way of yahoo. i do so much work with google finance's data feed in google sheets since i run a few community competitions in them. would have had to call it quits on those if the lights did not turn back now. was super excited to see everything back this afternoon! :banana:
     

Share This Page