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.
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.
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!
@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
The googlefinance() function doesn't work, but there was some way of downloading the values themselves?
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...
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!
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.
@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
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.
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?
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?).
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.
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!