| A | B | C | |
|---|---|---|---|
1 | Sheet | Description | Web page link |
2 | Fidelity sheets | Fidelity Treasuries download includes all 600+ Treasuries, so I use this for BEI calculations. If you don't have a Fidelity account, you'll need to get quotes from somewhere else. Fidelity downloads include the download date and time in the last row of column A, so this can be used to reliably set the settlement date. | |
3 | FidoTIPS | TIPS ask quotes downloaded from Fidelity using Replace data at selected cell (A1, green background) | |
4 | FidoNom | Treasury ask quotes downloaded from Fidelity using Replace data at selected cell (A1, green background) | |
5 | FidoSAO | Seasonally adjusted (SA) TIPS ask yields, also with outlier factors (SAO), using FidoTIPS. Set all the outlier factors to 1.0000 if you just want SA with no O. | |
6 | BEI | Breakeven inflation calculations using FidoSAO and FidoNom; includes BEI from last know ref CPI date as well as from settlement date | |
7 | |||
8 | Schwab sheets | Deleted. They serve no additional purpose beyond the Fidelity sheets, so I don't want to bother updating them in this spreadsheet. | |
9 | |||
10 | Reference sheets | ||
11 | RefCpiSA | Reference CPI lookup table used for seasonal adjustments, calculated with CPI data from sheet CpiSA | |
12 | CpiSA | Five years of CPI non-seasonal and seasonal CPIAUC imported from FRED with IMPORTDATA. | |
13 | RefCpi | Reference CPI lookup table used for calculating index ratios, calculated with CPI data from sheet CPI. | |
14 | CPI | Recent CPI non-seasonal CPIAUC imported from FRED with IMPORTDATA. | |
15 | RefCpiSaHelper | Hidden. Same as refCpiSA, but uses helper columns, so the formulas are simpler. | |
16 | TIPSref | TIPS reference, used to get dated date ref CPI values for OUTSTANDING TIPS, which is all that's required for this spreadshet, using tips_cpi_data_summary from FiscalData | https://fiscaldata.treasury.gov/datasets/tips-cpi-data/reference-cpi-numbers-and-daily-index-ratios-summary-table |
17 | TIPSrefAll | Hidden. TIPS reference, used to get dated date ref CPI values for ALL TIPS, using auction data from FiscalData. Included just for informational purposes; useful for calculating historical index ratios for TIPS that are no longer outstanding. | https://fiscaldata.treasury.gov/datasets/treasury-securities-auctions-data/treasury-securities-auctions-data |
18 | Holidays | Holiday lookup table, used to determine trade date and settlement date | |
19 | TIPSindexRatios | TIPS ref CPI and index ratios using tips_cpi_data_detail from FiscalData for selected dates. Not referenced in any other sheets; just used to quickly look up index ratios for specific dates. | https://fiscaldata.treasury.gov/datasets/tips-cpi-data/reference-cpi-numbers-and-daily-index-ratios-summary-table |
20 | CUSIProots | List of CUSIP roots and which type of Treasury each is used for. Used to create charts of yields by Treasury type. | |
21 | Canty | Screenshots of selected formulas from the Canty SA paper | |
22 |