Oct 06 2020 02:13 PM
Using the stocks feature on Excel, I want to be able to put a formula in a set of cells that keeps up with a stock's closing prices for every day of the month and will update after every day. I've tried several things but can't seem to figure out how to get the previous close feature to correspond with given dates. Can anybody give me a formula or some sort of guidance? Thanks
Oct 06 2020 09:03 PM
I would be happy to know if I could help you.
Oct 06 2020 09:16 PM
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.
Oct 06 2020 10:56 PM - edited Oct 07 2020 01:49 AM
Solution@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.
Oct 06 2020 10:56 PM - edited Oct 07 2020 01:49 AM
Solution@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.