Forum Discussion
How do I get closing prices for days of the month to update every day using Excel 365 Stocks?
collinreed I don't believe the Stock data type can give you historic prices. It has Price and Previous Close. Of course, you can start building your own history file by writing some code to copy the Price for the day and paste it into a history table and then run you reports/analysis from there. But, if you forget, or are not able to update the schedule one or a few days, you need to add data manually.
Perhaps best to find an on-line source that allows you to read out historic stock quotes for the securities you are interested in. Then use Power Query (PQ) to connect to this data and have it extract what you need for whatever date or period you are looking for. Though, I'm pretty sure you'll not find any source that can give you prices on future dates.
Edit: Found a (probably one of many) site that allowed me to dynamically link to historic stock data. The attached file is just an example of what's possible with PQ. This particular site requires 9 different parameters to build a dynamic URL. The parameter sheet gives an example of how an initial static URL compares to a dynamic URL. Hope you find this helpful.
I understand that, the issue is simply I would like to attach a particular date, whether it be in the future or already happened and for the closing price of that day to update to the sheet over time.
collinreed I don't believe the Stock data type can give you historic prices. It has Price and Previous Close. Of course, you can start building your own history file by writing some code to copy the Price for the day and paste it into a history table and then run you reports/analysis from there. But, if you forget, or are not able to update the schedule one or a few days, you need to add data manually.
Perhaps best to find an on-line source that allows you to read out historic stock quotes for the securities you are interested in. Then use Power Query (PQ) to connect to this data and have it extract what you need for whatever date or period you are looking for. Though, I'm pretty sure you'll not find any source that can give you prices on future dates.
Edit: Found a (probably one of many) site that allowed me to dynamically link to historic stock data. The attached file is just an example of what's possible with PQ. This particular site requires 9 different parameters to build a dynamic URL. The parameter sheet gives an example of how an initial static URL compares to a dynamic URL. Hope you find this helpful.