Model Approach for creating a stock index

[sb_user]

I am trying to create a value weighted index across the positions in a portfolio

I have the below Inputs from excel which I load into seperate tables in the model

- Prices for each security for each day over the last two years. Dates along the left column

- Trades comprimising the portfolio

To create the Index I need to do the following

- Use the Trades to find the size of each position on each day

- Multiply each position by the respective price for each day to get the value of each position on each day

-  Divide the value of each position by the total value of the portfolio to get the weighting of each portfolio

- create the the Index value by combing all Prices combing with their value weighting

I currently have two tables (Prices, Trades) as well as a date table. I am struggling with the approach in terms of creating new tables to perform the steps above, or store the calculations in measures. Any tips greatly appreciated

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[littlemojopuppy]

Hi @sb_user 

This doesn't seem terribly difficult at first glance...can you provide some sample data to play with?

[sb_user]

Hi - Sure,

I have created some dummy date which hopefully makes it easier.

Inputs 1 = Prices

Price
	Stock A	Stock B	Stock C	Stock D	Stock E
01/01/2022	23.65	6.52	15.1	24.8	26.35
02/01/2022	24	7.38	15.7	25.55	28.15
03/01/2022	24.1	6.91	14.78	25.6	28.55
04/01/2022	24.75	6.61	15.84	26.7	29.8
05/01/2022	25.5	6.19	16.6	24.6	30.55
06/01/2022	25.55	6.4	17	24.8	30.95
07/01/2022	25.35	6.5	17.08	24.8	30.8
08/01/2022	25	6.4	17.44	24.5	30.3
09/01/2022	25.15	6.55	16.58	23.75	31.75
10/01/2022	25.6	6.71	16.9	24.25	31.5
11/01/2022	25.6	6.79	17.3	25	29.9
12/01/2022	25.3	6.78	17.12	24.65	29.35
13/01/2022	24.55	6.72	17.26	24.25	29.15
14/01/2022	25.2	6.85	18.1	24.1	28.1
15/01/2022	25.65	6.82	19.46	23.8	28.15
16/01/2022	24.7	6.73	20	23.15	26.6
17/01/2022	24.7	6.73	20	23.15	26.6
18/01/2022	24.7	6.73	20	23.15	26.6
19/01/2022	24.7	6.73	20	23.15	26.6
20/01/2022	25.05	6.32	19.72	23.15	26.8

Inputs 2 = Trades

Trades
01/01/2022	Stock A	100
01/01/2022	Stock B	400
03/01/2022	Stock C	550
04/01/2022	Stock A	-3
05/01/2022	Stock C	-2
08/01/2022	Stock A	-4
08/01/2022	Stock B	-10
08/01/2022	Stock C	-13
11/01/2022	Stock A	-20
11/01/2022	Stock C	-10
11/01/2022	Stock E	140
13/01/2022	Stock A	-20
13/01/2022	Stock C	-5
13/01/2022	Stock B	-1
13/01/2022	Stock D	40
15/01/2022	Stock C	-12
15/01/2022	Stock D	-3
15/01/2022	Stock B	-4
17/01/2022	Stock D	-1
17/01/2022	Stock B	-6
17/01/2022	Stock C	-9

From the Tables I need to find the Total number of shares for each day. I have tried creating a cumulative measure for this in Power BI, but unsure how this should be stored. (I have a large data set with 40 + Stocks, and over 2 years of prices. But ideally, I need something like the below, Either implicitly or explicitly in the model

Running Sharecount

Shares
	Stock A	Stock B	Stock C	Stock D	Stock E
01/01/2022	100	400	0	0	0
02/01/2022	100	400	0	0	0
03/01/2022	100	400	550	0	0
04/01/2022	97	400	550	0	0
05/01/2022	97	400	548	0	0
06/01/2022	97	400	548	0	0
07/01/2022	97	400	548	0	0
08/01/2022	93	390	535	0	0
09/01/2022	93	390	535	0	0
10/01/2022	93	390	535	0	0
11/01/2022	73	390	525	0	140
12/01/2022	73	390	525	0	140
13/01/2022	53	389	520	40	140
14/01/2022	53	389	520	40	140
15/01/2022	53	385	508	37	140
16/01/2022	53	385	508	37	140
17/01/2022	53	379	499	36	140
18/01/2022	53	379	499	36	140
19/01/2022	53	379	499	36	140
20/01/2022	53	379	499	36	140

Then from this I need to times the Price for each stock * the shares held for each stock. For each day, again unsure how this should be stored in the model, but the values would be below

Shares
	Stock A	Stock B	Stock C	Stock D	Stock E
01/01/2022	100	400	0	0	0
02/01/2022	100	400	0	0	0
03/01/2022	100	400	550	0	0
04/01/2022	97	400	550	0	0
05/01/2022	97	400	548	0	0
06/01/2022	97	400	548	0	0
07/01/2022	97	400	548	0	0
08/01/2022	93	390	535	0	0
09/01/2022	93	390	535	0	0
10/01/2022	93	390	535	0	0
11/01/2022	73	390	525	0	140
12/01/2022	73	390	525	0	140
13/01/2022	53	389	520	40	140
14/01/2022	53	389	520	40	140
15/01/2022	53	385	508	37	140
16/01/2022	53	385	508	37	140
17/01/2022	53	379	499	36	140
18/01/2022	53	379	499	36	140
19/01/2022	53	379	499	36	140
20/01/2022	53	379	499	36	140

Mkt Value
	Stock A	Stock B	Stock C	Stock D	Stock E	Total
01/01/2022	2,365.00	2,608.00	-	-	-	4,973.00
02/01/2022	2,400.00	2,952.00	-	-	-	5,352.00
03/01/2022	2,410.00	2,764.00	8,129.00	-	-	13,303.00
04/01/2022	2,400.75	2,644.00	8,712.00	-	-	13,756.75
05/01/2022	2,473.50	2,476.00	9,096.80	-	-	14,046.30
06/01/2022	2,478.35	2,560.00	9,316.00	-	-	14,354.35
07/01/2022	2,458.95	2,600.00	9,359.84	-	-	14,418.79
08/01/2022	2,325.00	2,496.00	9,330.40	-	-	14,151.40
09/01/2022	2,338.95	2,554.50	8,870.30	-	-	13,763.75
10/01/2022	2,380.80	2,616.90	9,041.50	-	-	14,039.20
11/01/2022	1,868.80	2,648.10	9,082.50	-	4,186.00	17,785.40
12/01/2022	1,846.90	2,644.20	8,988.00	-	4,109.00	17,588.10
13/01/2022	1,301.15	2,614.08	8,975.20	970.00	4,081.00	17,941.43
14/01/2022	1,335.60	2,664.65	9,412.00	964.00	3,934.00	18,310.25
15/01/2022	1,359.45	2,625.70	9,885.68	880.60	3,941.00	18,692.43
16/01/2022	1,309.10	2,591.05	10,160.00	856.55	3,724.00	18,640.70
17/01/2022	1,309.10	2,550.67	9,980.00	833.40	3,724.00	18,397.17
18/01/2022	1,309.10	2,550.67	9,980.00	833.40	3,724.00	18,397.17
19/01/2022	1,309.10	2,550.67	9,980.00	833.40	3,724.00	18,397.17
20/01/2022	1,327.65	2,395.28	9,840.28	833.40	3,752.00	18,148.61

From this I can create the index value by a Summing (for all equity),  Equity Price * ( Equity Mkt Value / Total Mkt Value ). e.g. =(EqPrice1*EqMv1/MktValTotal) For 1 to N

Which gets me the below

Index Value
01/01/2022	14.666481
02/01/2022	14.8329148
03/01/2022	14.8332602
04/01/2022	15.6209484
05/01/2022	16.3322419
06/01/2022	16.5857627
07/01/2022	16.58256
08/01/2022	16.7348514
09/01/2022	16.1748173
10/01/2022	16.4759551
11/01/2022	19.5728254
12/01/2022	19.2816709
13/01/2022	19.3354405
14/01/2022	19.445118
15/01/2022	20.1712634
16/01/2022	19.9488254
17/01/2022	19.9733105
18/01/2022	19.9733105
19/01/2022	19.9733105
20/01/2022	19.962572

Then as a final stage I index this the initial Index to 100

Index Base
01/01/2022	100
02/01/2022	101.13479028
03/01/2022	101.13714510
04/01/2022	106.50781497
05/01/2022	111.35760477
06/01/2022	113.08617712
07/01/2022	113.06434012
08/01/2022	114.10270395
09/01/2022	110.28424129
10/01/2022	112.33747969
11/01/2022	133.45277174
12/01/2022	131.46760226
13/01/2022	131.83421773
14/01/2022	132.58202863
15/01/2022	137.53308250
16/01/2022	136.01644027
17/01/2022	136.18338662
18/01/2022	136.18338662
19/01/2022	136.18338662
20/01/2022	136.11016846

Any help very much appreciated! In reality my data set is a little more complicated, but its mainly the model concept I am struggling with (how many tables I need). Once I have this i will be able to figure out the nuances