Help with query

udigold1

New Member
Joined
Dec 15, 2008
Messages
45
Hi there,
I want to know if there's a way to brake-down to components a data table according to weight?
I have a portfolio of equities and etf's (table 1) , and I want that every ETF in it will be 'opened' according to the weights (table 3) of its related index (table 2) , so I'll get a table like in the 'Desired Query'.
Is that doable?

Thanks in advance


Table 1

Portfolio componentsBalance
SPDR10,000
AAPL5,000
MSFT4,000
QQQ3,000

<tbody>
</tbody>

Table 2

Portfolio componentsLinked Index
SPDRS&P500
QQQNasdaq100

<tbody>
</tbody>

Table 4
IndexSectorWeight
S&P500Technology20%
S&P500Energy40%
S&P500Consumer Staples30%
S&P500Consumer Discretionary10%
Nasdaq100Technology15%
Nasdaq100Energy15%
Nasdaq100Consumer Staples30%
Nasdaq100Consumer Discretionary40%

<tbody>
</tbody>


Desired Query result

Portfolio componentsBalance
SPDR -Technology2,000
SPDR -Energy4,000
SPDR -Consumer Staples3,000
SPDR -Consumer Discretionary1,000
AAPL5,000
MSFT4,000
QQQ -Technology300
QQQ -Energy450
QQQ -Consumer Staples450
QQQ -Consumer Discretionary900

<tbody>
</tbody>
 
Last edited:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Yes, you would join the two tables (with an inner join in sql or by dragging one field to the other from the table windows in design view of the query).
 
Upvote 0
So I would join Table 1 with Table 2, Table 2 to Table 4 and create a calculated field of table4.weight * Table1.balance, concatenate Table2.PortfolioComponents and Table4.Sector

HTH
 
Upvote 0
Hi, I did the following select statement but it only returned results for stocks that appear in table 2.

Code:
SELECT [Table1].[balance]*[table4].[weight] AS Expr1, Table1.id_num
FROM (Table1 INNER JOIN Table2 ON Table1.id_num = Table2.id_num) INNER JOIN Table4 ON Table2.index_id = Table4.index_id;
 
Upvote 0
Try changing the join type, the default I believe is only records that match in both tables.
Otherwise I'd try a UNION with records that are not in table 2.

You might have to do two queries then, the first to get everything in table 1 and two. Then a second to link 2 and 4.

HTH
Hi, I did the following select statement but it only returned results for stocks that appear in table 2.

Code:
SELECT [Table1].[balance]*[table4].[weight] AS Expr1, Table1.id_num
FROM (Table1 INNER JOIN Table2 ON Table1.id_num = Table2.id_num) INNER JOIN Table4 ON Table2.index_id = Table4.index_id;
 
Upvote 0
Got it! thank you!
it's a union with a left join :)

Code:
SELECT Table1.id_num, Table1.shovi*table3.weight AS Expr1
FROM Table1 INNER JOIN (Table3 INNER JOIN Table2 ON Table3.index_id = Table2.index_id) ON Table1.id_num = Table2.id_num
union all
SELECT Table1.id_num, Table1.shovi 
FROM Table1 
LEFT JOIN Table2 ON Table1.id_num = Table2.id_num
WHERE (((Table2.id_num) Is Null));
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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
Back
Top