Vanguard GICS Importer

Table of Contents

• Description
  • New Approach
  • TLDR
  • What Is This Tool?
• Goal
• How to use
  • Output
• TODO

Description

New Approach

The new approach will be to use Fidelity Website as a source. Unfortunately there’s no public API according to their customer service so this data has to be crawled.

❗	This README will be updated in the next days if there’s a plan on how to tackle the challenge of the crawler for Fidelity.

TLDR

Import ETF .xlsx-files provided by Vanguard and receive the distribution of their shares grouped by Global Industry Classification Standard (GICS) Industries by MSCI.

What Is This Tool?

This tool lets you import the positions Excel files for ETFs provided by Vanguard (see section Aufschlüsselung der Positionen e.g. of the Vanguard FTSE Developed World ETF). After importing, it will match the given data of the "Sektor" column against the Industries of the GICS.

Goal

The goal is to make this tool available to get better data quality for custom excel sheets or for tools like Parqet.

How to use

1. Download ETF positions excel from Vanguard (see Description for an exapmle link)

2. Check out this tool via Git

3. Run yarn install in root dir

4. Place your downloaded files in data folder

5. Change path in index.ts (will be removed, see TODO)

6. Run npx ts-node index.ts in root dir

Output

Using the given data example representing the Vanguard FTSE Developed World ETF this tool will print the following JSON output:

{
 "suggestion": {
  "jsonpatchFormat": true,
  "security": "REPLACEME",
  "diff": [
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "452020",
     "share": 5.118
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "451030",
     "share": 7.302
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "251020",
     "share": 2.535
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "402010",
     "share": 1.12
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "352020",
     "share": 5.667
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "303020",
     "share": 1.507
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "302020",
     "share": 1.494
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "401010",
     "share": 5.108
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "502020",
     "share": 0.929
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "502010",
     "share": 0.609
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "252030",
     "share": 0.574
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "551010",
     "share": 1.685
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "151010",
     "share": 1.061
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "252010",
     "share": 0.337
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "203010",
     "share": 0.483
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "302030",
     "share": 0.595
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "201050",
     "share": 1.537
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "352010",
     "share": 1.21
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "402030",
     "share": 1.047
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "201010",
     "share": 1.217
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "451020",
     "share": 1.241
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "302010",
     "share": 0.622
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "255040",
     "share": 0.535
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "151040",
     "share": 1.1
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "202020",
     "share": 0.194
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "551030",
     "share": 0.557
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "201030",
     "share": 0.23
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "251010",
     "share": 0.419
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "601010",
     "share": 0.137
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "151030",
     "share": 0.212
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "151020",
     "share": 0.067
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "253010",
     "share": 0.302
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "551050",
     "share": 0.085
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "203020",
     "share": 0.073
    }
   },
   {
    "op": "add",
    "path": "/industries/-",
    "value": {
     "id": "101020",
     "share": 0.013
    }
   }
  ]
 }
}

❗	I’m still adding the mapping of descriptions used by Vanguard to the GICS Industry descriptions, so the output at this time is just a fraction of the final result.

TODO

• Extend matching by creating a mapping table (support needed)

• specify output format (possible JSON orientating on Parqet data format)

  • maybe generate request and send it to specifiable API endpoint

• make multiple imports possible (needed?)