Talk:US domiciled ETFs that are UK HMRC reporting funds

No longer just Vanguard funds
In recent months, the universe of UK-reporting US ETFs which might interest the typical Boglehead has grown beyond Vanguard funds - in particular a good slate of offerings from Dimensional and iShares have gained UK-reporting status. In light of this, TedSwippet and others on the Non-US Investing forum judged it would be a good idea to:
 * expand the page beyond just Vanguard ETFs, to include at least the new Dimensional and iShares funds
 * automate generation of the list so that new funds can be easily added as they appear (more below)

As listing every UK-reporting US ETF would include an awful lot of non-Bogleheady dross, I've tried to keep to the following criteria to determine which funds should be excluded:


 * Sector ETFs, focusing on individual industries (exception: real estate/REIT funds)
 * Geographic ETFs, narrowly focusing on countries/regions (exceptions: Europe/Asia developed markets, and of course US-specific funds)
 * Active ETFs (exceptions: Dimensional or Avantis funds, which have large fanbases on the Bogleheads forums)
 * "Spindex" funds - where a fund tracks an "index" controlled by or constructed to the requirements of the ETF provider, to provide a veneer for marketing and legal purposes of passive "index investing" over a traditional active strategy
 * Leveraged or inverse ETFs
 * Alternative assets (e.g. carbon credits, cryptocurrency, commodities with the exception of gold)
 * Alternative strategies (e.g. long/short funds, options strategies including buy/write)

Note that these criteria now exclude some Vanguard sector funds which previously were on this list (VAW, VCR, VDC, VDE, VFH, VGT, VHT, VIS, VOX, VPU).

--Baron greenback 16:44, 29 December 2022 (EST)

Table generation (new method)
Generation of the list of ETFS, including downloading the latest HMRC spreadsheet and matching it up to live CUSIP/ISIN-to-ticker mapping data, can now be fully automated using a Python script. The script is on GitHub here: https://github.com/bhbarongreenback/uk-reporting-etfs

I also use this script to generate a "spillover list" of UK-reporting US ETFs which don't meet the criteria for this page.

Documentation of the previous method of preparing the list has been preserved below in case we need to fall back to this at some point in the future.

--Baron greenback 16:44, 29 December 2022 (EST)

Table generation (old Google Sheets method)
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)