Before/After using UNIQUE function on 2 arrays

[gcefaloni]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
8	CUSIP	Pos MM$	TICKER	COUPON RATE	MATURITY DATE	LAST_PRICE	YIELD	YAS_YLD_SPREAD	G-Spread Percentile between MIN/MAX Hist. Spread.	YAS_BNCHMRK_SECURITY_DES	DUR_ADJ_MID	PV01	$PV01	INT_ACC	Amount MM$	Inc Accr	SECTOR 1	SECTOR 2	Rating	NXT CPN	COUNTRY	CPN_TYP	Base Currency	Settle	NAME	Avg Cost	Coupon Frequency
9	25675TAD9	1.000	DOLCN	2.34%	22-Jul-21	101.205	1.258%	102.5	63.7	CAN 3 1/4 06/01/21	1.10	1.13	113	0.864	1.012	1.021	CORP	Consumer, Cyclical	BBB	-	CANADA	FIXED	CAD	3-Sep-19	DOLLARAMA INC	100.370	2
10	47787ZBW1	0.444	DE	2.70%	12-Oct-21	101.920	1.263%	100.4	89.6	CAN 0 3/4 09/01/21	1.32	1.36	60	0.399	0.453	0.454	CORP	Industrial	A	-	CANADA	FIXED	CAD	5-Feb-19	JOHN DEERE CANADA FND IN	100.165	2
PORTF

Proposed changes:

Security selection dashboard - 2020-04-30.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	TT	CUSIP	SetDt	Side	Qty (M$)	Broker	Order	TIF	AllocAccount	Pos MM$	Inc Accr	Cash Flow	Balance	PX_LAST	Objective Inc Accr	Name	Maturity Date	Rating	LQA_LIQUIDITY_SCORE
2	I	06415ELY9	06/05/20	S	1000		LMT	DAY	17-71064	1.000	1.071	$ 1,070,924		105.611	1070	BANK OF NOVA SCOTIA	18-Jan-24	BBB+	77
3	I	651333FS0	06/05/20	B	1710		LMT	DAY	17-71064	1.710	1.794	-$ 1,794,113	-$ 723,189	104.9	1800	PROVINCE OF NEWFOUNDLAND	2-Jun-25	A	95
4	I	651333FZ4	06/05/20	B	1550		LMT	DAY	17-71064	1.550	1.673	-$ 1,673,185	-$ 2,396,374	107.924	1680	PROVINCE OF NEWFOUNDLAND	2-Jun-28	A-	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!

 

[Fazza]

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]

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]

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.