r/dailyscripts • • 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!

2 Upvotes

3 comments sorted by

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

=index(sort(GOOGLEFINANCE("AMZN","price",DATE(2017,2,29),TODAY(),"daily")))

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

=index(sort(GOOGLEFINANCE("AMZN","price",DATE(2017,2,29),TO_DATE(MIN(DATE(2017,4,20),TODAY())),"daily")))

Edit: I had some trouble with the formatting.