How do I get closing prices for days of the month to update every day using Excel 365 Stocks?

[collinreed]

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

[#2]

@collinreed 

Maybe this information will help you.

Update the data

Linked data types can be linked to an online data source. Whenever you convert text to a linked data type, an external data connection is created in the workbook. If the data changes online, you can update it in Excel to bring it up to date. To update the data, right-click a cell with the associated data type and select Data Type> Update. This will update the selected cell and any other cells that have the same data type.

To update all related data types and all data connections that might be in the workbook (including queries, other data connections, and PivotTables), choose Data> Update All or press CTRL + ALT + F5.

Excel data types: Stocks and geography

https://support.microsoft.com/en-us/office/excel-data-types-stocks-and-geography-61a33056-9935-484f-...

I would be happy to know if I could help you.

Nikolino
I know that I don't know (Socrates)

[#3]

@NikolinoDE 

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. 

[#4 · Accepted 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.