1

I have a MySQL table with around 900 entries of different ETF holdings. Here is an example:

ID    ETF Name                                       Stock Name           Weighting     ISIN
1     iShares Automation & Robotics                  XIAOMI CORP          3.45          KYG9830T1067
24    iShares Automation & Robotics                  SNAP INC CLASS A     3.37          US83304A1060       
42    iShares Automation & Robotics                  APPLE INC            2.14          US0378331005       
51    iShares MSCI World Information Technology      APPLE INC            20.14         US0378331005
53    iShares MSCI World Information Technology      MICROSOFT CORP       14.04         US5949181045

My goal is now to set ETF weightings, for example for the ETF iShares Automation & Robotics 20% and for the ETF iShares MSCI World Information Technology 80%. What the SQL query should now do is to calculate the weighted holding in dependence of the 20/80 ETF diversification.

For example for the stock named Apple the result would be: 0.2 * 2.14 + 0.8 * 20.14 = 16.54. At the end I would like to have one table which removes all duplicates (by ISIN) and gives me one table with the calculated weights ordered by weightings.

Here is the expected output (for 20/80):

Stock Name            Weighting     ISIN
APPLE INC             16.54         US0378331005
MICROSOFT CORP        11.232        US5949181045
XIAOMI CORP           0.69          KYG9830T1067
SNAP INC CLASS A      0.674         US83304A1060

Sadly I don't even have a clue where to start... You can match the stocks by using the ISIN which stands for International Securities Identification Number and is an unique stock id!

I would appreciate any kind of help!

Jan
  • 1,180
  • 3
  • 23
  • 60

1 Answers1

2

You can do it with conditional aggregation:

SELECT `Stock Name`,
       ROUND(SUM(CASE 
             WHEN `ETF Name` = 'iShares Automation & Robotics' THEN 0.2 
             WHEN `ETF Name` = 'iShares MSCI World Information Technology' THEN 0.8
           END * Weighting
       ), 3) Weighting,     
       ISIN 
FROM tablename
GROUP BY `Stock Name`, ISIN

See the demo.
Results:

> Stock Name       | Weighting | ISIN        
> :--------------- | --------: | :-----------
> APPLE INC        |    16.540 | US0378331005
> MICROSOFT CORP   |    11.232 | US5949181045
> SNAP INC CLASS A |     0.674 | US83304A1060
> XIAOMI CORP      |     0.690 | KYG9830T1067
forpas
  • 160,666
  • 10
  • 38
  • 76
  • 2
    Just noticed you got past 100k. Congrats! – Nick Jan 03 '21 at 11:32
  • Thanks mate.. unfortunately this is sadly not working for my db... because the names are not always exactly the same, thats why you have to match it by ISIN. How can I do these? For example one ETF just says "Apple" and the other one says "APPLE INC" – Jan Jan 03 '21 at 11:56
  • @Jan then try this: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=d59c6eab0ccd45c84e09eb05de1d0500 but you will get 1 of the names of each ISIN. – forpas Jan 03 '21 at 12:15
  • 1
    THANKS MATE, it is now working. You are a legend! – Jan Jan 03 '21 at 12:40