Before/After using UNIQUE function on 2 arrays

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
Hi,

I'm trying to build a before/after picture of a portfolio I have after some changes are made to it. So I have an array that represents the bond/stock positions I have and I have another array that shows the changes (Buys and Sells) that I will make. My objective is to see what the portfolio would hypothetically look like once the changes have been made.

I was thinking of using the UNIQUE function in order to do that but the issues I'm having are 2 fold:
1 - I do not know how to use the unique function on 2 separate arrays and merge it into one array (objective is to find the unique "bond/stock tickers" in the current portfolio and then find the same tickers in the array that represents BUYS/SELLS and then net the position after the change. If the position is not in the portfolio add it, if the position is sold completely, remove it).
2 - If we assume we can use the unique function on 2 arrays and it finds the unique tickers, how do I combine positions quantities so that if there are additional buys on current positions or partial sells on current positions, that the remaining quantities are adjusted in order to do that "picture of the portfolio after the proposed changes".

This is what it looks like:

Current portfolio:
CORE PORT.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
8CUSIPPos MM$TICKERCOUPON RATEMATURITY DATELAST_PRICEYIELDYAS_YLD_SPREADG-Spread Percentile between MIN/MAX Hist. Spread.YAS_BNCHMRK_SECURITY_DESDUR_ADJ_MIDPV01$PV01INT_ACCAmount MM$Inc AccrSECTOR 1SECTOR 2RatingNXT CPNCOUNTRYCPN_TYPBase CurrencySettleNAMEAvg CostCoupon Frequency
925675TAD9 1.000 DOLCN2.34%22-Jul-21101.2051.258%102.563.7CAN 3 1/4 06/01/211.101.13 113 0.864 1.012 1.021 CORPConsumer, CyclicalBBB - CANADAFIXEDCAD3-Sep-19DOLLARAMA INC100.3702
1047787ZBW1 0.444 DE2.70%12-Oct-21101.9201.263%100.489.6CAN 0 3/4 09/01/211.321.36 60 0.399 0.453 0.454 CORPIndustrialA - CANADAFIXEDCAD5-Feb-19JOHN DEERE CANADA FND IN100.1652
PORTF


Proposed changes:
Security selection dashboard - 2020-04-30.xlsx
ABCDEFGHIJKLMNOPQRS
1TTCUSIPSetDtSideQty (M$)BrokerOrderTIFAllocAccountPos MM$Inc AccrCash FlowBalancePX_LASTObjective Inc AccrNameMaturity DateRatingLQA_LIQUIDITY_SCORE
2I06415ELY906/05/20S1000LMTDAY17-710641.0001.071 $ 1,070,924 105.6111070BANK OF NOVA SCOTIA18-Jan-24BBB+77
3I651333FS006/05/20B1710LMTDAY17-710641.7101.794-$ 1,794,113 -$ 723,189 104.91800PROVINCE OF NEWFOUNDLAND2-Jun-25A95
4I651333FZ406/05/20B1550LMTDAY17-710641.5501.673-$ 1,673,185 -$ 2,396,374 107.9241680PROVINCE OF NEWFOUNDLAND2-Jun-28A-98
4. TSOX Staging



I would like to find unique values in the "CUSIP" column and then merge the "Inc Accr" (which is a quantity column represented in thousands) column based on whether they are buys or sales (Column D in the changes array). The end output would be an array showing the different characteristics of each position but with combined quantities. Can I get by only using the unique function? Do I also need to use the filter function? How do I add/substract the quantities from the 2 arrays? These are all questions I'm juggling with right now.

Thank you so much for your help!
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
It isn't clear to me exactly what is wanted - and the reason I can't give a very specific answer.

I expect a pivot table is a good approach - it can combine data from multiple sources, account for addition or subtraction depending on "Side" values being "S" or "B", multiply "Inc Accr" values by 1000 if needed. These manipulations being done in the dataset definition (SQL) just the same as a query in a database.
 

gcefaloni

Board Regular
Joined
Mar 15, 2016
Messages
117
It isn't clear to me exactly what is wanted - and the reason I can't give a very specific answer.

I expect a pivot table is a good approach - it can combine data from multiple sources, account for addition or subtraction depending on "Side" values being "S" or "B", multiply "Inc Accr" values by 1000 if needed. These manipulations being done in the dataset definition (SQL) just the same as a query in a database.

Would it be possible to do that without using pivot tables so that it can be refreshed dynamically in real-time? It would be better to use formulas to do it even if they are somewhat complicated formulas, if it is at all possible that is.

As another example:

Is it possible to use the new "unique" function on multiple non-contiguous arrays? If so, what would be the proper syntax to make it work?

I'd like to do something like:
=UNIQUE({'Sheet1'!A1:A10,'Sheet2'!A1:A10}) where both Sheet 1 and Sheet 2 column As have the same type of data and have common data points. I want the unique ones to show up in a single array.

So, looking up unique values in non-contiguous arrays would be the objective. Is that doable? If not with the "UNIQUE" function, what other function could do that?
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
I can't help with functions for that.

I wonder if the there may be a simpler solution. Such as a column added to the portfolio worksheet with something like SUMIFS to pick up the results for the CUSIP & Side.

As I wrote initially, it isn't clear exactly what is wanted. There may be benefit from asking the question afresh with samples of not only the inputs but also the corresponding results.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,504
Messages
5,625,191
Members
416,079
Latest member
lizziebee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top