Bond portfolio automation

Hi everyone, looking to build a way to update automatically a bond portfolio based on the transactions (similar to a stock inventory management).
One of the challenges I see is to implement the cost basis methodology (instead of an average price, which is good for now).

Attaching sample for illustration.

Thanks!

Bondportfolio_automated.xlsx

I'm not exactly sure of what you're looking for. Can you fill in the EXPECTED results in the spreadsheet?

Bondportfolio_automated2.xlsx

I have added an example here to show how transactions get booked into portfolio (condition should be sum of notional for a specific ISIN is <>0).

Why isn't XS1910828182 showing up in Portfolio? Its notional is not = 0.
And why is XS1953057491 showing twice? I thought maybe because of the "Side", but then the "Notionals" don't add up the same as you show.

Bondportfolio_automated3.xlsx

Apologies for the mistakes. Correct file uploaded.

Thanks again

Try this,

Please Login or Register  to view this content.

Here's another option:

=LET(u,UNIQUE(D24:J27),
x,XLOOKUP(INDEX(u,,2),E4:E15,C4:C15,,0),
n,SUMIFS(K4:K15,E4:E15,INDEX(u,,2)),
a,XLOOKUP(INDEX(u,,2),E4:E15,L4:L15,,0),
b,XLOOKUP(INDEX(u,,2),E4:E15,M4:M15,,0),
h,HSTACK(x,u,n,a,b),
FILTER(h,INDEX(h,,9)<>0))

This assumes that the transactions for each ISIN will be in date order, which your sample is and makes sense that this would be the case.

Questions:
You state that the date in Column C could be the oldest for that ISIN. My results for ISIN XS1953057491 show a date of 31-May-19, but your example and Windknife's both show a date of 09-Mar-20. Why that date if 31-May-19 is older?

Also same question as Hans - I get a negative 100,000 for XS1953057491, but you and windknife both show positive 100,000. Why is that?

Thank you windknife! Works spot on (and I am learning loads here).

Thank you Gregb11 for your comment.
You are absolutely right on the date selection. I should technically use the FIFO methodology (first trades to be booked in will be sold first) to get the accurate price on the remaining stock in inventory.

- Re the date: by default, excel would pick the oldest date when there are multiple transactions on the same ISIN. I need to look further into windknife's formula. Please bear with me
- Re XS1953057491: yes the amount should have been -100k (apologies one more time). That is the reason why I need to automate my spreadsheet.

You are welcome.

Thanks for rep and feedback!

I tried with Power Query and it was very easy!

Formula:

Please Login or Register  to view this content.

The real action goes on #"Linhas Agrupadas1" and #"Linhas Agrupadas" the rest is adjustment.