Talk:US domiciled ETFs that are UK HMRC reporting funds

Table generation
Generating the data for the table in this page can be partly automated. One method for doing this is as follows. This method assumes that you have access to a Google account within which you can create spreadsheets.

Within Google 'docs', create a new Google spreadsheet
There are no special requirements for this spreadsheet. The data set that will be imported into it is large.

Configure tab Sheet1 to contain the main HMRC 'reporting funds' data

 * Download HMRC's Excel spreadsheet to a local file. The spreadsheet is an XLSM document, linked to from this HMRC page: &#91;link&#93;.
 * Import the downloaded file into Sheet1 of your Google spreadsheet. Unless changed while saving the download, the file will have a name like 2022-06-10-Master-weblist.xlsm.

Configure tab Sheet2 to contain a mapping of CUSIPs to fund exchange tickers

 * Add a new sheet to your Google spreadsheet, Sheet2. Set focus to this sheet.
 * In another browser tab, visit DTCC's page of CUSIPs and fund exchange tickers, found here: &#91;link&#93;.
 * Use your browser to copy-and-paste from the table in this page directly into the clean Sheet2 tab created above.
 * Select all data in Sheet2, and explicitly format it into text. Copying and pasting data using a browser does not preserve data formats, but we want everything in Sheet2 to be text, so that lookups and matches work correctly with it.

Use formulas in tab Sheet3 to combine Sheet1 and Sheet2 into usable data

 * Add a new sheet to your Google spreadsheet, Sheet3. Set focus to this sheet.
 * Enter values and formulas as follows:
 * A1:
 * B1:
 * C1:
 * D1:
 * A2:
 * B2:
 * C2:
 * D2:
 * Select D2:D and format these cells as dates.
 * Note: The above formulas will copy down all the matching data. Column D of the HMRC data set is the fund name, column F is the fund CUSIP, column G is the date the fund gained reporting status, and column H (if set) is the date the fund lost reporting status. The formulas in A2, C2 and D2 select Vanguard funds from HMRC data that have a CUSIP and active reporting status. Column A of the DTCC data set is the fund exchange ticker, and column B the fund CUSIP. The formula in B2 retrieves fund exchange tickers from DTCC data, given CUSIPs. These formulas use functions that are specific to Google docs and so will (probably) not work in Excel.

Convert data from Sheet3 into wikitable format

 * Export tab Sheet3 into a local CSV format file.
 * Open the CSV file as text (renaming it to a TXT file will allow you to open it in a browser).
 * Select the entire CSV text data with your browser, and copy and paste it into an online CSV to wiki table converter: &#91;link&#93;.
 * Select the converted wiki table format text, and then copy and paste that into the main wiki page.

Simple!

DTCC's page of CUSIPs and fund exchange tickers
For convenience, and in case it goes away at some point, here is a snapshot of the DTCC CUSIP to fund exchange ticker data as of 12 Jun 2022, used in the process above, in CSV format. Source: &#91;link&#93;.

--TedSwippet 15:40, 8 November 2019 (UTC) --TedSwippet 06:00, 12 June 2022 (EDT) (update)