+ Reply to Thread
Results 1 to 7 of 7

Historical stock/inventory pricing by unit (triple lookup criteria)

  1. #1
    Registered User
    Join Date
    05-11-2021
    Location
    U.S.
    MS-Off Ver
    Office 365
    Posts
    4

    Post Historical stock/inventory pricing by unit (triple lookup criteria)

    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
    Attached Files Attached Files
    Last edited by ripvanbob; 05-11-2021 at 04:14 PM. Reason: Adding spreadsheet

  2. #2
    Registered User
    Join Date
    05-11-2021
    Location
    U.S.
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Historical stock/inventory pricing by unit (triple lookup criteria)

    Still looking for any assistance on this if anyone can help. Thank you.

  3. #3
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Historical stock/inventory pricing by unit (triple lookup criteria)

    This proposal employs helper columns (P:AA) which may be moved and/or hidden for aesthetic purposes.
    The helper columns are populated using: =IFERROR(INDEX(Transactions!$I$2:$I$26,AGGREGATE(14,6,(ROW(Transactions!$I$2:$I$26)-ROW(Transactions!$I$1))/(Transactions!$C$2:$C$26=B$1)/(Transactions!$A$2:$A$26<=$A2),1))*B2,"")
    Column N is populated using: =SUM(P2:AA2)
    Note that there was one discrepancy, however I made a notation that I feel explains why.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  4. #4
    Registered User
    Join Date
    05-11-2021
    Location
    U.S.
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Historical stock/inventory pricing by unit (triple lookup criteria)

    Thank you, JeteMc for the response!

    First, good catch on the discrepancy and the correction in my attached file; you are correct it should be I12, not I2.

    Second, I'm not familiar with the AGGREGATE function, so I'm learning something new by your usage.
    Since I'll be continuously adding more transactions, I'd prefer to use whole column references (e.g. A:A) instead of specific references that will need to be modified with each new transaction (e.g. A2:A26, etc.).
    After converting the references to columns, would adding a "+1" at the end of the ROW-ROW reference work?

    I'm trying to post the actual formula change but am receiving an error so I'll try to describe it.
    In cell Q2 of your sheet, I changed specific cell references (e.g. $I$2:$I26) to column references (e.g. $I:$I) and after the second ROW reference I added "+1" (-ROW(Transactions!$I$1)+1).

    I performed a simple test and it worked, but would appreciate any feedback.

    Thank you!

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Historical stock/inventory pricing by unit (triple lookup criteria)

    I am not a fan of whole column references as for some Excel functions it will slow down calculations.
    I would suggest the following instead:
    1. Convert the range on the Transactions sheet into an Excel table.
    2. Modify the formula in P2 (over and down) using structured references so that it reads:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that in the attached file the table is named TransactionsTable and that the formula automatically adjusted when a new row (27) was added to the table.
    Let us know if you have any questions.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-11-2021
    Location
    U.S.
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Historical stock/inventory pricing by unit (triple lookup criteria)

    Yes, that works as well. Thanks again for the assistance!

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Historical stock/inventory pricing by unit (triple lookup criteria)

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] lookup and calculate stock taken from inventory
    By Por2gal in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-10-2019, 01:45 AM
  2. Determining unit price from a pricing matrix...
    By pmantey in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-17-2014, 10:34 PM
  3. Placing Unit Price into Pricing Brackets - Use IFSTATEMENTS?
    By contactlewis in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-16-2014, 01:36 PM
  4. I need a stock order sheet to be created from a stock inventory
    By tamhav in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-20-2014, 10:40 AM
  5. Replies: 4
    Last Post: 01-29-2013, 02:26 AM
  6. [SOLVED] Historical Stock Prices in Excel
    By [email protected] in forum Excel General
    Replies: 1
    Last Post: 02-10-2006, 10:50 PM
  7. historical stock prices
    By Stevo in forum Excel General
    Replies: 1
    Last Post: 01-24-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1