NOTE: To make it easier to see what I'm after, I'm attaching a mini-spreadsheet. Ultimately, what I'm looking for is on the Price History tab in Column N (Daily Market Value). I've manually calculated what the results should be for that given date, but I'm looking for a formula to do this.
I have a workbook that tracks stock portfolio transactions (buys, sells, splits, dividends, etc.) on one tab (TRANSACTIONS) that also records net cumulative units per transaction. I also have a separate tab (PRICE HISTORY) that captures the daily closing price of all equity holdings by ticker symbol. On any given day, I can see the present daily portfolio value by equity and the total value. However, I'm wanting to track the historical daily portfolio value based on the number of net cumulative shares held on any given day. I've tried several combinations of VLOOKUP, INDEX, & MATCH, SUMIF, and PIVOT TABLE but can't quite seem to get the right combo since it seems to be a triple lookup (return Cumulative Units of all outstanding equities based on Stock ticker symbol for a given date; then multiply that by the lookup of the historical price to get price per equity position, then grand total for daily historical portfolio market value). I want to be able to track the daily total portfolio market value so that I can perform other calculations (e.g. MTD, QTD, YTD returns, etc.), and to also chart it. I can't simply use the current daily value because I don't always get the chance to record it everyday. While I can find several dozen stock portfolio tracker templates, I can't find one that tracks daily historical portfolio values based on then-current shares per equity position. Although I'm ultimately looking for all historical data, I provided price history tables In the examples to see the significant portfolio value changes on 7/21/2020 before the purchases on 7/22/2020 (it should only register 40 shares of PG), the portfolio value on 7/22/202 after the purchase of the 10 equities, and the value on 1/29/2021 to see the value and correct shares after the sale transactions.
If it helps, another way to think of this is to determine a historical product inventory market value for any given day by product. Should be the same concept.
Thank you!
Example:
Transactions
Transactions.JPG
Price History (1)
Price History.JPG
Price History (2)
Price History2.JPG
Bookmarks