Trying to make my Stock Spreadsheet work

[Minghao Liao]

I am trying to make my stock workbook by having 2 sheets. One where I enter transactions and one where I can see a stock summary. 

I will enter information in my sheet "Transactions(SG)" and I would like to see the changes in the "Stock Summary(SG)"

I have a problem with column G and H in "Stock Summary(SG)". What I am trying to achieve for column G is to reflect the current units I have. This should come from "Transactions(SG)", after I find the  latest entry of the stock in Transactions and then take the CumVol for that row. I cannot get a formula to reflect what I want. 

For Column H, I want the same as Above but for the CumCost. 

Can someone assist. Thanks

[#2 · Accepted solution]

Try updating your formulas to these:

G2: {=IFERROR(INDEX('Transactions(SG)'!$J$2:$J$24,SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-ROW('Transactions(SG)'!$J$2:$J$24)+1),"No Transactions")}

H2: {=IFERROR(INDEX('Transactions(SG)'!$O$2:$O$24,SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-ROW('Transactions(SG)'!$O$2:$O$24)+1),"No Transactions")}

Please see attached file for additional reference.  You may need to change the "No Transactions" portion of the formula to 0 to avoid error messages.  This will happen if there is no transaction associated with the symbol.

[#3]

Thanks a lot. It works. 

However, appreciate if you could explain to me how 

SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-ROW('Transactions(SG)'!$J$2:$J$24)+1) gives you the row of what I want. How does the SUMPRODUCT work here? I understand the MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24) which gives me the highest row that the name appears. but the rest I do not really understand.

[#4]

---

---

I understand the MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24) which gives me the highest row that the name appears. but the rest I do not really understand.

Pretty darn close.....  The way the name is evaluated in the SumProduct() function is a 1 or 0 based on whether the value is a match.  Then this 1 or 0 is multiplied by the row that this value appears on.  So 1 x Row Number.....  The higher the row number the higher the value, which is why the Max function is able to tell us which Row is the highest number row that also contains the matching name criteria. 

[#5]

As variant if not to use array formula this works for G

=IFERROR(  
 INDEX('Transactions(SG)'!$J$2:$J$24,  
  MATCH(
     AGGREGATE(14,6,1/(A2='Transactions(SG)'!$C$2:$C$24)*'Transactions(SG)'!$A$2:$A$24,1),
     'Transactions(SG)'!$A$2:$A$24,0),
 0),
"No Transaction")

similar for next column

[#6]

I still do not understand why there is a need for  minus ROW('Transactions(SG)'!$J$2:$J$24)+1) in the formula.

I realise that after the SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24))) gives me the highest Prev row, the portion above is basically -2+1. Could I just enter a generic -1 instead of the formula above?

[#7]

Could you explain the 

1/(A2='Transactions(SG)'!$C$2:$C$24)*'Transactions(SG)'!$A$2:$A$24 part of the formula? )

[#8]

Hello-

You are correct you may use the -1,  as such:

={IFERROR(INDEX('Transactions(SG)'!$J$2:$J$24,SUMPRODUCT(MAX(('Transactions(SG)'!$C$2:$C$24=A2)*ROW('Transactions(SG)'!$C$2:$C$24)))-1),"No Transactions")}

I used an old formula from another workbook and updated it to fit your references.  Not sure, but I believe the portion you are asking to omit was used to offset different data structures.  i.e. the header rows may have started from different rows so there was a need to adjust the Index reference.

[#9]

This is used to extract the max transaction date for X company (Stock Summary(SG)!A2).  It looks at all of the values that match x company along side with the date and then returns the max date of this subset from the transactions worksheet.  Which is then used in the Index / Match portion of the formula.

[#10]

Minghao,

That is as Matt explained.

1/(A2='Transactions(SG)'!$C$2:$C$24)

is the filter which returns 1 if the company name matches, an error otherwise. Multiplying  on date and have the list of dates for that company with errors for other companies. AGGREGATE returns the latest date ignoring all errors. Similar way you may add more filters (e.g. for Buy/Sell).

For better understanding you may check how the formula works clicking in ribbon on Formula->Evaluate formula calculating it step by step.