Forum Discussion

Minghao Liao's avatar
Minghao Liao
Copper Contributor
May 21, 2018
Solved

Trying to make my Stock Spreadsheet work

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 wo...
Formulas & Functions
Need Help
  • Matt Mickle's avatar
    Matt Mickle
    May 21, 2018

    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.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 21, 2018

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

Minghao Liao's avatar
Minghao Liao
Copper Contributor
May 22, 2018

Could you explain the 

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

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 22, 2018

    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.

     

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    May 22, 2018

    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.

Resources