Single-cell stock allocations

[Anonymous]

I am looking for a solution preferably in DAX, or alternatively in M/Power Query.

My dataset consists of a number of investors' stock portfolios. The number of stocks in each portfolio varies across investors (e.g. one may invest in 2 stocks, another in 10), and so do their allocations towards each.

Each portfolio is stated in a single row in a single column (B), in the following format: Stock 1/5%:Stock 2/10%:Stock 3/85%. Of course the stocks have actual names, this is simply an example.

While the number of stocks in a portfolio is variable, the range of stocks investors can choose is fixed and can be categorized as either Europe, U.S., or Asia stocks.

Ultimately, what I want to calculate is the allocation towards each of these 3 regions (e.g. may Investor X have 10% in European stocks, 85% in American, and 5% in Asian). My current solution in Excel is to extract the stock names, lookup their region in a lookup table, and then sum the amounts in each.

However, my formula is not the most straight-forward, and I cannot seem to convert it to DAX, as I am now moving my calculations into PowerPivot. Here is the formula: 

=SUMPRODUCT(TEXT(TRIM(MID(SUBSTITUTE($B4&$G$4:$G$15;"%";REPT(" ";100));FIND($G$4:$G$15;SUBSTITUTE($B4&$G$4:$G$15;"%";REPT(" ";100)))+LEN($G$4:$G$15&"/");100));"0,000;0;0;\0")*($H$4:$H$15="U.S."))/100

Unfortunately I have not been able to attach the sample workbook, but instead I have enclosed an image of the sheet below. Thanks a lot in advance, let me know if you need further.

[camargos88]

Hi @Anonymous ,

Check this file: Download PBIX 

Now just input the other tables and relate it.

If you consider it as a solution, please mark as a solution and kudos.

Ricardo

[Anonymous]

Great, definitely works better.

One issue, however. As mentioned, I know the range of stocks available, however, they won't be called "stock 1", "stock 2" etc., but instead "Amazon", "Facebook", etc.

How I look at it, this step is going to be an issue. Any suggestions around this? I have a list of all the stocks available (similar to the lookup table in the sample) classified by region.

[camargos88]

@Anonymous ,

Now you can import the table with the stock name and relate them.

Also if you want, import the stock name and merge it with the initial table. If you need help with that, just paste the table name here.

Ricardo

[Anonymous]

I am not sure I understand.

Since the "Added column" step relies on finding the name "Stock", won't my query break as no stocks are actually called "stock", but rather "Amazon", etc.?

Best,

Phill

[camargos88]

@Anonymous ,

Based on your initial post, how do you know the name of stock 1 ?

I just see table with region that we can use to map it.

Ricardo

[Anonymous]

I have a list of e.g. 100 stocks that investors can choose from.

However, which ones the investors actually choose (and how many) I won't know in advance. All I know is that the stock name will appear in the formatting mentioned.

So, a stock in the sample looking something like:

• Stock 1/5%/5000:Stock 2/10%/10000:Stock 3/85%/85000

Would be something like this in the real dataset:

• Amazon/5%/30000:Facebook/10%/10000:Apple/85%/80000

The key here is the format: Stock / allocation in % / allocation i USD. Does it make sense?

Here is an adjusted sample:

Table 1: Portfolios

CPR	Portfolio
Investor 1	Amazon/5%:Facebook/10,315%:Apple/85%
Investor 2	Amazon/30%/30000:Uber/20%/20000/Johnson & Johnson/50%/50000
Investor 3	VISA/40%/4000:Microsoft/20%/2000/Amazon/30%/3000:Facebook/10%/1000
Investor 4	Amazon/5%/500,25:Facebook/10%/1000,50:Apple/85%/8004
Investor 5	Amazon/30%/30000:Uber/20%/20000/Johnson & Johnson/50%/50000
Investor 6	VISA/40%/4000:Microsoft/20%/2000/Amazon/30%/3000:Facebook/10%/1000
Investor 7	Amazon/5%/500,25:Facebook/10%/1000,50:Apple/85%/8004
Investor 8	Amazon/10%/1000:Facebook/60%/6000:Uber/20%/2000:JPMorgan/10%/1000
Investor 9	VISA/40%/4000:Microsoft/20%/2000/Amazon/30%/3000:Facebook/10%/1000
Investor 10	Amazon/5%/500,25:Facebook/10%/1000,50:Apple/85%/8004
Investor 11	Amazon/30%/30000:Uber/20%/20000/Johnson & Johnson/50%/50000
Investor 12	VISA/40%/4000:Microsoft/20%/2000/Amazon/30%/3000:Facebook/10%/1000

Table 2: Lookup table (ignore the regions)

Name	Region
Amazon	Asia
Facebook	U.S.
Apple	Europe
Microsoft	Asia
Uber	U.S.
JPMorgan	U.S.
VISA	Europe
Johnson & Johnson	Asia
Alphabet	U.S.
IBM	U.S.
Tesla	U.S.
Twitter	Europe

[camargos88 · Accepted solution]

@Anonymous ,

I've updated the file:  Download PBIX 

Ricardo

[Anonymous]

Excellent, worked a charm.

One last enquiry. Is there anyway to reach the end result directly in PowerQuery.

So, instead of:

I'd prefer: 

The reason being that I have additional data where keeping the row context is ideal.

[camargos88]

Hi @Anonymous ,

It's possible, however you can do it easily using matrix. Also this format you are gonna have more workaround for calculations.

Ricardo

[camargos88]

@Anonymous ,

Yes, once you have the real data, it will split by your delimiters ( / and : )

Ricardo

[Anonymous]

Would you care to update the file based on the tables I just added?

I still cannot seem to make it work for some reason haha.

Sorry for the inconvenience!

[Anonymous]

Hi again,

Works well in PowerBi, thanks.

However, I am actually working in Excel (2013) with PowerQuery/PowerPivot add-ins (sorry if I was unclear about this), and for some reason, I do not have the "split into rows" option.

What to do in this case?

Best,

Phil

[camargos88]

Hi @Anonymous ,

I've updated the file with another table. This new code has another approach to handle it.

I hope it works.

Ricardo

[camargos88]

Hi @Anonymous ,

I believe that using power query you can split each stock in rows.

So you can relate the tables.

Can you paste the values here ?

Ricardo

[Anonymous]

I agree, but I am not sure whether it will work, as I do not know the number of stocks in each portfolio beforehand and hence neither whether to split across 5, 10, 20 columns etc.

Anyways, if you have a flexible solution, I'd really appreciate your input. Here is the sample data.

Investor 1	Stock 1/5%:Stock 2/10%:Stock 3/85%
Investor 2	Stock 1/30%/30000:Stock 5/20%/20000/Stock 8/50%/50000
Investor 3	Stock 7/40%/4000:Stock 4/20%/2000/Stock 1/30%/3000:Stock 2/10%/1000
Investor 4	Stock 1/5%/500,25:Stock 2/10%/1000,50:Stock 3/85%/8004
Investor 5	Stock 1/30%/30000:Stock 5/20%/20000/Stock 8/50%/50000
Investor 6	Stock 7/40%/4000:Stock 4/20%/2000/Stock 1/30%/3000:Stock 2/10%/1000
Investor 7	Stock 1/5%/500,25:Stock 2/10%/1000,50:Stock 3/85%/8004
Investor 8	Stock 1/10%/1000:Stock 2/60%/6000:Stock 5/20%/2000:Stock 6/10%/1000
Investor 9	Stock 7/40%/4000:Stock 4/20%/2000/Stock 1/30%/3000:Stock 2/10%/1000
Investor 10	Stock 1/5%/500,25:Stock 2/10%/1000,50:Stock 3/85%/8004
Investor 11	Stock 1/30%/30000:Stock 5/20%/20000/Stock 8/50%/50000
Investor 12	Stock 7/40%/4000:Stock 4/20%/2000/Stock 1/30%/3000:Stock 2/10%/1000