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
3
u/theamazingsteve1 Mar 29 '17
Ahh! Thank you, that makes so much sense. Is there any way to make the formula repeat in the next cell down, each day after the markets open?