Stock value month by month

[jpkroehling]

I have a spreadsheet file with three sheets: “Holdings”, “Quotes” and “Monthly”.

On Holdings, I have data about stocks I own:

• Column A - Stock symbol
• Column B - Buy Date
• Column C - Sell date
• Column D - Quantity
• Column E - Buy Value
• Column F - Sell Value
• Column G - Value now (=IF(F2>0, 0, VLOOKUP(A2,Quotes.$A$1:$C$429,3,0)*D2))

On the second sheet (Quotes), I have the prices for all relevant stocks:

• Column A - Symbol
• Column B - Date
• Column C - Price

On the third sheet (Monthly), I would like to get an idea on how the stock was on a set of months:

• Column A - Date (end of month) =EOMONTH(DATE(2014, 3, 1), 0) for A1 and =EOMONTH(A2, 1) for A2 and on
• Column B - Total

My problem is in getting the formula for the column B on the third sheet (Monthly). If I add one column for each stock, I can get the individual values using INDEX(...MATCH()...), but this has some problems:

• Couldn’t get the “sell date” to work on this situation, ie, couldn’t skip the calculation for the month of April if a stock was sold that month
• For each new stock, I would have to add a new column.
• For each new month, I have to copy and manually change the formula for each column (ie, each stock), as it says “the arrays have to be worked together” or something like this, resulting in Err504 if I just drag the formula.

What would be a proper way in getting the totals for column B on the third spreadsheet?

I made a sample spreadsheet is available for download . In the last sheet, I’ve added the expected values on the column D, with the columns E to G with the values per stock.

[mariosv]

Attached a couple of solutions:
monthly-stock-holdings-value_Sumproduct+PivotTable.ods

1. with SUMPRODUCT() function, but maybe a formula a bit long.
   =Holdings.$D$3*(Holdings.$G$3>0)*SUMPRODUCT(MAX((Quotes.$A$1:$A$999=F$1)*(MONTH(Quotes.$B$1:$B$999)=MONTH($A2))*Quotes.$B$1:$B$999)=Quotes.$B$1:$B$999;Quotes.$A$1:$A$999=F$1;Quotes.$C$1:$C$999)
   with MAX() we get the max date with quote, then looking for the quote of this max day for the specific product.

2. with Pivot table we can get a table with the max dates with quotes for every product.
   In this case, we need to set up year and months with F12, after put date in row fields, then edit date fields as is showed in the screenshoot.

[jpkroehling]

You got me closer to where I was, and I’m grateful for that! My problem, though, is getting the total on column B, without the helpers that I added with the expected values (columns E-H). Any ideas?

[jpkroehling]

In any case, I’m marking this as the correct answer, as I was able to get what I wanted based on your answer. In the end, I’m indeed using one column per ticker, as I can get charts per ticket this way. Thanks!