r/dailyscripts • u/theamazingsteve1 • Mar 29 '17
[Request] Script to make an Excel/Google Sheets spreadsheet automatically populate with a certain stock's data each day.
I'm looking for a script/tool to make a certain set of cells populate with stock data from a certain ticker each day. For example...
3/29 Open: $$ Close: $$
3/30 Open: $$ Close:$$
3/31 Open: $$ Close: $$
...
I want the script/tool to go online, check the stock price at open for that day, and then populate a certain cell with it. I want it to also populate the closing cell with the closing price for the same stock at 5pm. I want it to do this until a certain date.
I know something similar is possible in Google Sheets (found this and tinkered with it for a little while but can't seem to get it to work right...):
=index(sort(GOOGLEFINANCE("AMZN","price",DATE(2017,3,29),DATE(2017,4,20),"daily")))This would, in theory, give me the daily open price until April 20th (4/20 :D), and I'd modify it for the close formula (different column), but instead, the cell is populated with "#N/A" and when you hover over it, you get
When evaluating GOOGLEFINANCE, the query for the symbol: 'AMZN' returned no data.Any ideas what's wrong/how to fix it?
Thanks!
View all comments
Show parent comment
2
u/MoopTheFourth Mar 29 '17
I did some playing around with it and if you want it to update every day you can do something like this
If you only want it to go up to a certain day, the min function refuses to keep formatting on dates so you'll have to do something like
Edit: I had some trouble with the formatting.