Relate Your Tickers
Mutual funds and ETFs are common ways to invest. They can also be related in several ways to each other. Representing these relationships in your personal finance software enables interesting analyses. For example, you can get Beancount to analyze your asset allocation based on asset classes, with a minimal manual specification. Or have Beancount help you tax loss harvest.
Relationship Types
First, let’s look at some useful relationships between tickers:
-
Equivalents:
- Different classes of the same mutual fund: VTSMX, VTSAX, and VITSX are different classes (Investor, Admiral, Institutional) of Vanguard’s Total Stock Market fund. They may differ in minimums and expense ratios, but are otherwise the same.
- ETF equivalents of mutual funds: VTSAX is also available as an ETF: VTI
-
“Substantially identical securities“ is a US specific legal term that is used to determine whether a wash sale occurred when one attempts to do tax loss harvesting (TLH). Funds across brokers that track the same index (eg: S&P 500) may be substantially identical. Equivalent funds definitely are.
- TLH partners: when tax loss harvesting, one wants to track the performance of the fund being sold, while avoiding a wash sale by not replacing it with a “substantially identical” one. We call the substitute funds we can use, “TLH partners”. Funds based on major market indexes thus have well known substitutes. For example, some are shown under “Substitute funds” here. You may have your own TLH partners based on the funds and brokers you use, and your personal legal sense of what may constitute a substantially identical fund.
Specifying Relationships
Beancount allows commodities to be declared with arbitrary metadata, which is the perfect place to specify relationships.
2005-01-01 commodity VTI
name: "Vanguard Total Stock Market Index Fund"
a__equivalents: "VTSAX"
a__substidenticals: "VTSMX,VTSAX"
tlh_partners: "FXAIX,VFIAX,VOO,VLCAX,VV"
Why do we use the a__ prefix above? This is explained in the next section below.
Building Complete Relationships from Partial Specifications
Soon, it becomes apparent that manually writing out a complete specification of relationships is a pain. We should be able to make a few, minimal specifications, and our system should be able to make obvious inferences. For example, if we write:
2005-01-01 commodity VTI
a__equivalents: "VTSAX"
2005-01-01 commodity VTSAX
then our system should be able to fill in the missing metadata:
2005-01-01 commodity VTI
a__equivalents: "VTSAX"
2005-01-01 commodity VTSAX
a__equivalents: "VTI"
This is exactly what ticker-util does. You can view equivalent relationships using
ticker-util relate find-equivalents
Equivalent funds are by definition substantially identical (though the reverse is not true). In the real world, it gets slightly more complicated with input like this:
2005-01-01 commodity ITOT
a__substidenticals: "SPY"
2005-01-01 commodity SPY
a__substidenticals: "VOO"
2005-01-01 commodity VOO
a__equivalents: "VFIAX"
With the input above, ticker-util makes all the inferences possible, and writes the
output:
ticker-util add --cf x.bc --from-file
ticker-util add --cf x.bc --tickers VFIAX
ticker-util comm --cf x.bc --include-undeclared
produces:
2005-01-01 commodity ITOT
a__substidenticals: "VFIAX,VOO,SPY"
2005-01-01 commodity SPY
a__substidenticals: "VFIAX,ITOT,VOO"
2022-10-15 commodity VFIAX
a__equivalents: "VOO"
a__substidenticals: "ITOT,VOO,SPY"
2005-01-01 commodity VOO
a__equivalents: "VFIAX"
a__substidenticals: "VFIAX,ITOT,SPY"
The a__ prefix denotes an automatically generated metadata field. However,
a__equivalents and a__substidenticals are both manual input fields, and are
automatically generated (and overwritten) fields. You initially specify the partial
input, and ticker-util rewrites these field with a complete specification from your
partial input.
Using these Relationships
Why bother relating tickers? You can:
-
Summarizing Data: Handy references are always…handy. Some tables I find useful,
and display in Fava:
- currently held tickers along with their equivalents
- ETFs with their mutual fund equivalents
- Substantially identical tickers for each ticker (to help tax-loss harvest)
- Tax loss harvesting: this is where these relationships truly help. With minimal specification, you can get Beancount to have a complete view of possible tax loss partners for each fund, alert you when possible wash sales could occur, and so on. See [[ Making Tax Loss Harvesting with Beancount Smoother ]]