Treasury.io Data Dictionary

Brian Abelson edited this page Oct 5, 2013 · 59 revisions

treasury bureau of the fiscal service csv soundsystem

This file contains an explanation of the tables and items published daily by the U.S. Financial Management Service, as made available by the Treasury.io API:

http://www.treasury.io/

Table of Contents

General Concept and Purpose
Source Data
Data Coverage
Data Format
Data Units
Data Structure
  Table I (T1)
  Table II (T2)
  Table III
    Table III A (T3A)
    Table III B (T3B)
    Table III C (T3C)
  Table IV (T4)
  Table V (T5)
  Table VI (T6)
Contact
Credits

General Concept and Purpose

The Financial Management Service (FMS) is the cash management arm of the U.S. Treasury. It provides central payment services for federal government agencies, manages the government’s debt collections and deposits and does the accounting and reporting of its daily operations.

Because the reporting is not provided in a machine-readable format, we’ve created the Treasury.io API to go through the FMS’s statements and make them available to the public in a format that can be easily searched, aggregated and analyzed.

Source Data

Treasury.io downloads and parses the “Daily Treasury Statement” published by the FMS at 4pm each day the U.S. government is open for business.

The Daily Treasury Statement contains the daily cash operations of the United States Treasury: spending, taxing, borrowing, day-by-day, program-by-program. The source files are available for viewing here:

https://fms.treas.gov/fmsweb/DTSFilesArchiveAction.do

Data Coverage

Text versions of Daily Treasury Statements are available to June 2005; prior years going back to January 1998 are available in PDF format.

Currently, Treasury.io contains all the text files, going back to 2005; the PDF files are not included in the API output.

Data Format

Data parsed from the Daily Treasury Statements is stored as a SQLite Version 3 database using the following formats, per SQLite:

  • INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).

For more on the SQLite data formats referenced above, see the SQLite documentation: http://www.sqlite.org/datatype3.html

Data Units

All money amounts presented in the data are in millions of U.S. dollars, rounded to the nearest million. The dollar amounts are not adjusted for inflation.

Data Structure

Each Daily Treasury Statement contains six sets of tables, which provide the following data:

Table I

T1 | Table I summarizes the operating cash balance of the U.S. Treasury. Think of it as Uncle Sam’s checkbook balance. This table answers the question, “how much money does Uncle Sam have in his checking account on any given day?”

Below is a screenshot of what Table I looks like in the Daily Treasury Statement:

table I screenshot

The table sums the different accounts in which the federal government keeps money and sums them into a total cash balance by day, month and fiscal year.

T1 Fields and Definitions

Table I can be accessed in the Treasury.io API by querying table “T1” – short for Table I. Below are the fields and definitions for the table. Fields marked in italics were added to the table by the API; all others are original to the data:

Field Datatype Definition
table text The name of the table containing the data, in this case Table I
url text The URL for the text file containing the data on the Financial Management Service website
date text Date for balance data contained in the table, in YYYY-MM-DD format
year_month text Year and month for balance data contained in the table, in YYYY-MM format
year integer Year for balance data contained in the table, in YYYY format
month integer Month for balance data contained in the table, in ascending order from 1 (January) to 12 (December)
day integer Day of the month for the balance data
weekday text Day of the week for balance data (Monday thru Friday)
account text Name of the account containing the cash balance data, standardized across time for variances in spelling and name. Please see the Treasury.io Field Definitions wiki page for definitions of the accounts in this table.
account_raw text Name of the account containing the cash balance data, in raw, unstandardized format
is_total_ integer Binary flag field indicating whether the account balance represents a total figure (1) or sub-component that adds up to the total (0). Exclude rows marked (1) from sum queries to prevent double-counting.
close_today integer Closing account balance as of the date marked in the date field
open_today integer Opening account balance as of the date marked in the date field
open_mo integer Opening account balance for the month marked in the month field
open_fy integer Opening account balance as of the start of the federal government’s fiscal year, which begins October 1 each year
footnote real Contains any footnotes provided in the data

Table II

T2 | Table II contains the daily deposits and withdrawals of cash to/from the Treasury’s cash account at the Federal Reserve – the Federal Reserve Account.

Think of this table as a checking account statement for the federal government. It answers the question, “what did Uncle Sam spend money on today, and how much?”

Below is a screenshot of what Table II looks like in the Daily Treasury Statement:

table II screenshot

The table sums the deposits and withdrawals to/from the Federal Reserve Account and uses the net difference to calculate the net change in the government’s cash-on-hand detailed in Table I. A related account for short-term investments is summed below the Federal Reserve account in similar format, but is no longer used by the U.S. Treasury (see Table V below).

Table II is broken into two ledgers: deposits and withdrawals:

  • Deposits represent money taken in by the federal government through its various agencies and programs.
  • Withdrawals represent money spent by the federal government through each of various agencies and programs.

Some programs, such as Medicare, appear on both sides of the ledger. To calculate the net impact of these programs on the Treasury’s cash balance, be sure to net together deposits and withdrawals for those programs.

T2 Fields and Definitions

Both ledgers can be accessed in Treasury.io by querying table “T2” - short for Table II. Below are the fields and definitions for the table. Fields marked in italics were added to the table by the API; all others are original to the data:

Field Datatype Definition
table text The name of the table containing the data, in this case Table II
url text The URL for the text file containing the data on the Financial Management Service website
date text Date for deposits and withdrawals data in the table, in YYYY-MM-DD format
year_month text Year and month for deposits and withdrawals contained in the table, in YYYY-MM format
year integer Year for the deposits and withdrawals contained in the table, in YYYY format
month integer Month for the deposits and withdrawals contained in the table, in ascending order from 1 (January) to 12 (December)
day integer Day of the month for the deposits and withdrawals contained in the table
weekday text Day of the week on which the deposits or withdrawals took place (Monday thru Friday)
account text name of the account impacted by the deposit or withdrawal, standardized across time for variances in spelling and name.
transaction_type text Indicates type of transaction: 1) “Deposit” for cash coming into the account through the agency or program named in the item field 2)“Withdrawal” for cash coming out of the account through the agency or program named in the item field.
parent_item text Certain items have a parent classification that appears indented above them in Table II. We call these parent_items. There are two parent_items on the deposit side of Table II: “Deposits by States” and “Other Deposits.” On the withdrawals side, there is only one: “Other Withdrawals.”
is_total integer Binary flag field indicating whether the deposit or withdrawal represents a total figure (1) or sub-component that adds up to the total (0). Exclude rows marked (1) from sum queries to prevent double-counting.
is_net integer Binary flag indicating whether the item represents a net of deposits and withdrawals (1) or just deposits or withdrawals (0). Treasury presents some programs separately on both sides of the ledger but nets others together, or lists them separately but later on begins netting them together. Use this flag to disambiguate between the two.
item text Indicates the government agency or program responsible for the deposit or withdrawal, standardized across time for variances in spelling and name. Some agencies appear on both sides of the ledger, meaning they have both deposits and withdrawals. Please see the Treasury.io Field Definitions wiki page for definitions of the items in this table.
item_raw text Item name in raw, unstandardized format
today real Deposits or withdrawals made by the agency or program on the date marked in the date field
mtd real Deposit or withdrawal made by the agency or program since the beginning of the month
fytd real Deposit or withdrawal made by the agency or program since the start of the federal government’s fiscal year, which begins October 1 each year
footnote text Contains any footnotes provided in the data

Table III

T3A | T3B | T3C | Table III summarizes the debt operations of the U.S. Treasury. Think of it as a credit card statement for Uncle Sam; it answers the question, “how much did Uncle Sam borrow today, and how close is he to his credit card limit?”

The table is divided into three sub-tables summarizing the government’s daily debt operations. Below are descriptions of the three tables, with screenshots of what they look like in the Daily Treasury Statement:

Table III A

T3A | (Public Debt Transactions): This table summarizes sales and repayments of U.S. government debt:

Table III A

The table is broken into two ledgers – issues and redemptions. The issues represent sales of U.S. debt, while redemptions detail repayments. Redemptions minus issues equal the net change in the U.S. public debt each day, month and fiscal year to date. If redemptions are greater than issues, then the public debt shrinks; when issues outpace redemptions, the public debt grows.

The data can be accessed in Treasury.io by querying table “T3A” – short for Table III A.

T3A Fields and Definitions

Below are the fields and definitions for T3A (Public Debt Transactions). Fields marked in italics were added to the table by the API; all others are original to the data:

Field Datatype Definition
table text The name of the table containing the data, in this case Table III A
url text The URL for the text file containing the data on the Financial Management Service website
date text Date for debt transaction data contained in the table, in YYYY-MM-DD format
year_month text Year and month for debt transaction data contained in the table, in YYYY-MM format
year integer Year for debt transaction data contained in the table, in YYYY format
month integer Month for debt transaction data contained in the table, in ascending order from 1 (January) to 12 (December)
day integer Day of the month on which the debt transaction data took place
weekday text Day of the week on which the debt transaction took place (Monday thru Friday)
transaction_type text Indicates whether the transaction is a sale of U.S. government debt (“issue”) or a repayment (“redemption”)
debt_type text The type of debt being issued: “marketable” or “nonmarketable.” Marketable debt is sold to the public; nonmarketable debt is an I.O.U. between federal agencies and the U.S. Treasury
parent_item text Certain items have a parent classification that appears indented above them in Table III A. We call these parent_items. There are two parent_items in Table III A: “Bills,” which include the items “Cash Management Series” and “Regular Series,” and “United States Savings Securities,” which include the items “Cash Issue Price” and “Interest Increment.”
is_total integer Binary flag field indicating whether the debt transaction amount represents a total figure (1) or sub-component that adds up to the total (0). Exclude rows marked (1) from sum queries to prevent double-counting.
item text Indicates the debt being sold or repaid, standardized across time for variances in spelling and name. Please see the Treasury.io Field Definitions wiki page for definitions of the items in this table.
item_raw text Item name in raw, unstandardized format
today real Amount of the debt sold or repaid on the date marked in the date field
mtd real Amount of the debt sold or repaid since the beginning of the month
fytd real Amount of the debt sold or repaid since the start of the federal government’s fiscal year, which begins October 1 each year
footnote real Contains any footnotes provided in the data

Table III B

T3B | (Adjustment of Public Debt Transactions to Cash Basis): This table converts sales and repayments of debt into a cash line item showing their impact on the Treasury’s Federal Reserve Account in Table II:

Table III B

The top half reconciles issues of public debt to a cash line item, while the bottom half reconciles repayments. Here is how the math works:

For debt issues:

“Public Debt Cash Issues (Table III-A)” is the starting line item to which:

Add:

  • Premium on New Issues

Why is this being added?

The premium represents the additional amount paid over the face value of U.S. debt. So it’s added to the amount reported in Table III A to get to the cash total.

Subtract:

  • Discount on New Issues – Bills and Bonds and Notes
  • Federal Financing Bank
  • Government Account Transactions
  • Hope Bonds
  • Interest Increment on United States Savings Securities
  • Inflation-Protected Securities Increment

Why are these being subtracted?

The discount on new issues represents the amount paid less than the face value of the debt paid being sold. It is subtracted from the amount reported in Table III A to get to the cash total.

The Federal Financing Bank, Government Account Transactions and Hope Bonds are subtracted because these are all methods of lending to other government agencies, so there is no net cash impact to the Treasury.

The Interest Increment on United States Savings Securities and Inflation-Protected Securities Increment represents interest that is accrued on these debts. The interest adds to the debt reported in Table III A but the cash value of the interest is only recognized when the debt is redeemed. So it must be subtracted to get to the cash impact to the Treasury.

That yields the “Total Public Debt Cash Issues Deposited in the Federal Reserve Account” line item at the bottom of the issues portion of the table.

For debt redemptions:

“Public Debt Redemptions (Table III-A)” is the starting line item, to which:

Add:

  • Premium on Debt Buyback Operation

Why is this being added?

The premium represents any additional amounts the Treasury must pay when buying back its debt, so it must be added to the amounts reported in Table III A to get to the actual cash impact for the Treasury.

Subtract:

  • Discount on Debt Buyback Operation
  • Federal Financing Bank
  • Government Account Transactions
  • Hope Bonds

Why are these being subtracted?

The discount represents amounts the Treasury paid less than the face value of the debt it was buying back, so it must be subtracted to the amounts reported in Table III A to get to the actual cash impact for the Treasury

The Federal Financing Bank, Government Account Transactions and Hope Bonds are subtracted because these are all methods of lending to other government agencies, so there is no net cash impact to the Treasury.

That yields the “Total Public Debt Redemptions Withdrawn from the Federal Reserve Acct.”

The data can be accessed in Treasury.io by querying table “T3B:” – short for Table III B.

T3B Fields and Definitions

Below are the fields and definitions for T3B (Adjustment of Public Debt Transactions to Cash Basis). Fields marked in italics were added to the table by the API; all others are original to the data:

Field Datatype Definition
table text The name of the table containing the data, in this case Table III B
url text The URL for the text file containing the data on the Financial Management Service website
date text Date for debt transaction data contained in the table, in YYYY-MM-DD format
year_month text Year and month for debt transaction data contained in the table, in YYYY-MM format
year integer Year for debt transaction data contained in the table, in YYYY format
month integer Month for debt transaction data contained in the table, in ascending order from 1 (January) to 12 (December)
day integer Day of the month on which the debt transaction data took place
weekday text Day of the week on which the debt transaction took place (Monday thru Friday)
transaction_type text Indicates whether the transactions being reconciled to a cash basis are debt sales (“Public Debt Cash Issues”) or repayments (“Public Debt Cash Redemptions”)
parent_item text Certain items have a parent classification that appears indented above them in Table III B. We call these parent_items. The only parent_item in Table III B is “Discount on New Issues,” which includes the items “Bills” and “Bonds and Notes.”
is_total integer Binary flag field indicating whether the debt transaction amount represents a total figure (1) or sub-component that adds up to the total (0). Exclude rows marked (1) from sum queries to prevent double-counting.
item text Indicates the debt sale or repayment transaction being adjusted to cash basis, standardized across time for variances in spelling and name. Please see the Treasury.io Field Definitions wiki page for definitions of the items in this table.
item_raw text Item name in raw, unstandardized format
today integer Amount of debt sold or repaid on the date marked in the date field
mtd integer Amount of debt sold or repaid since the beginning of the month
fytd integer Amount of debt sold or repaid since the start of the federal government’s fiscal year, which begins October 1 each year
footnote text Contains any footnotes provided in the data

Table III C

T3C | (Debt Subject to Limit): This table summarizes the amount of U.S. debt outstanding that it subject to the debt limit set by Congress and provides the prevailing debt limit.

Table III C

Not all debt issued by the U.S. government is subject to the Congressionally-mandated “debt limit.” By law, certain types of debt are exempt from the limit, like debt held by a Treasury subsidiary, the Federal Financing Bank.

Table III C takes the total public debt outstanding – the sum of debt held by investors and internal governmental I.O.U.s – and adjusts it by subtracting exempt debt types like the Federal Financing Bank. The total public debt is calculated in the first two line items, by adding up debt held by the public and intragovernmental holdings. This total is then decreased by exempt holdings listed below, such as the Federal Financing Bank, to arrive at the total debt subject to the debt limit in the bottom of the table. The prevailing debt limit is listed below that total, showing how close the government is to breaching the limit. __ The data can be accessed in Treasury.io by querying table “T3C” – short for Table III C.

T3C Fields and Definitions

Below are the fields and definitions for T3C (Debt Subject to Limit). Fields marked in italics were added to the table by the API; all others are original to the data:

Field Datatype Definition
table text The name of the table containing the data, in this case Table III C
url text The URL for the text file containing the data on the Financial Management Service website
date text Date on which the debt balance data contained in the table was recorded, in YYYY-MM-DD format
year_month text Year and month in which the debt balance data was recorded, in YYYY-MM format
year integer Year in which the debt balance data was recorded, in YYYY format
month integer Month in which debt balance data was recorded, in ascending order from 1 (January) to 12 (December)
day integer Day of the month on which the debt balance data was recorded
weekday text Day of the week on which the debt balance data was recorded (Monday thru Friday)
is_total integer Binary flag field indicating whether the debt balance represents a total figure (1) or sub-component that adds up to the total (0). Exclude rows marked (1) from sum queries to prevent double-counting.
parent_item text Certain items have a parent classification that appears indented above them in Table III C. We call these parent_items. In Table III C, the only two parent_items are “Debt Not Subject to Limit” and “Other Debt Subject to Limit.” These indicate whether the items they reference are or are not subject to the debt ceiling.
item text Names of different types of debt held by the U.S. government, standardized across time for variances in spelling and name. Please see the Treasury.io Field Definitions wiki page for definitions of the items in this table.
item_raw text Item name in raw, unstandardized format
close_today real The ending balance for each type of debt named in the item column as of the date indicated in the date column
open_today real The opening balance for each type of debt named in the item column as of the date indicated in the date column
open_mo real The opening balance for each type of debt named in the item column as of the month indicated in the month column
open_fy real The opening balance for each type of debt named in the item column at the start of the federal government’s fiscal year, which begins October 1 each year
footnote text Contains any footnotes provided in the data

Table IV

T4 | Table IV details the sources and uses of the federal government’s tax collections.

Tax receipts are aggregated into one deposit line item in Table II – “FTD’s Received,” short for “Federal Tax Deposits Received.” In Table IV, you can get a break-down of these taxes and find out how much came from individuals or corporations, for instance.

It's like your paycheck, which details how much you got paid and where the money went to - checking account, 401(k), health plan, etc. The table answers the question, “where is Uncle Sam getting his tax dollars from, and where is he putting them?”

Below is a screenshot of what Table IV looks like in the Daily Treasury Statement:

Table IV Screengrab

The table sums up the various sources of the federal government’s tax collections and then details the accounts into which the taxes were deposited. The sum total of the taxes collected each day, month and fiscal year to date equals the sum total of the deposits over the same periods.

T4 Fields and Definitions

Table IV can be accessed in the Treasury.io API by querying table “T4” – short for Table IV. Below are the fields and definitions for the table. Fields marked in italics were added to the table by the API; all others are original to the data:

Field Datatype Definition
table text The name of the table containing the data, in this case Table IV
url text The URL for the text file containing the data on the Financial Management Service website
date text Date for the tax collections contained in the table, in YYYY-MM-DD format
year_month text Year and month for the tax collections contained in the table, in YYYY-MM format
year integer Year for the tax collections contained in the table, in YYYY format
month integer Month for the tax collections contained in the table, in ascending order from 1 (January) to 12 (December)
day integer Day of the month on which the tax collections data took place
weekday text Day of the week on which the tax collections took place (Monday thru Friday)
type text This column indicates whether the classification refers to a source of tax revenue received by the government (“source”) or how it is used (“use”).
is_total integer Binary flag field indicating whether the tax amounts represent a total figure (1) or sub-component that adds up to the total (0). Exclude rows marked (1) from sum queries to prevent double-counting.
classification text For rows where the type column equals “source,” the classification gives the type of tax collected by the federal government. For rows where the type column equals “use,” the classification details what Treasury did with those tax deposits – for example, the name of the account it deposited them in. Total sources equal total uses each day. Please see the Treasury.io Field Definitions wiki page for definitions of the classifications in this table.
classification_raw text Classification name in raw, unstandardized format
today real Amount of taxes collected or deposited on the date marked in the date field
mtd real Amount of taxes collected or deposited since the beginning of the month
fytd real Amount of taxes collected or deposited since the start of the federal government’s fiscal year, which begins October 1 each year
footnote text Contains any footnotes provided in the data

Table V

T5 | The U.S. Treasury used to invest operating cash that it did not immediately need to fund the government’s operations in short-term investments. These investments were made through the short term investments account, detailed in Table V.

As footnoted in the Daily Treasury Statement, the Treasury scaled down and then suspended its short-term cash investment program beginning in November 2008. Treasury no longer uses the short-term investment account and, as of 30 March, 2012, Table V has not been used by the government.

As a result, the table, labeled “T5” in Treasury.io, has been deprecated and is no longer tracked by the API.

Table V Screengrab

Table VI

T6 | Table VI details how much tax money was refunded by the federal government according to two major breakdowns: tax refunds to individuals and businesses.

It’s like a refund on your credit card statement detailing a reversed charge or overpayment that’s been credited back. It answers the question, “how much tax money did Uncle Sam return, and to whom?”

Below is a screenshot of what Table VI looks like in the Daily Treasury Statement:

Table VI Screengrab

The table lists the tax refunds given to individuals and businesses each day, month and fiscal year to date. The refunds are labeled by mode of transfer – check or electronic funds transfer (“EFT”). The tax refunds tagged as EFT flow to the withdrawals ledger in Table II.

T6 Fields and Definitions

Table VI can be accessed by querying “T6” – short for Table VI. Below are the fields and definitions for the table. Fields marked in italics were added to the table by the API; all others are original to the data:

Field Datatype Definition
table text The name of the table containing the data, in this case Table VI
url text The URL for the text file containing the data on the Financial Management Service website
date text Date for the tax refunds contained in the table, in YYYY-MM-DD format
year_month text Year and month for the tax refunds contained in the table, in YYYY-MM format
year integer Year for the tax refunds contained in the table, in YYYY format
month integer Month for the tax refunds contained in the table, in ascending order from 1 (January) to 12 (December)
day integer Day of the month on which the tax refunds were processed
weekday text Day of the week on which the tax refunds were processed (Monday thru Friday)
refund_method text Indicates whether payment processing method for the tax refund: a check payment (“CHECKS”) or electronic funds transfer (“EFT”). The Treasury only began indicating processing methods an October 1, 2012, so prior to that date, this column contains no data.
refund_type text Gives the type of tax refund processed by the Treasury, standardized across time for variances in spelling and name into two categories: “Business” for income tax refunds to businesses and “Individual” for income tax refunds to individuals
refund_type_raw text Refund type name in raw, unstandardized format
today real Amount of tax refunds processed on the date marked in the date field
mtd real Amount of tax refunds processed since the beginning of the month
fytd real Amount of tax refunds processed since the start of the federal government’s fiscal year, which begins October 1 each year
footnote text Contains any footnotes provided in the data

Contact

For questions about using the API, email the support listserv and a member of the developer team will respond to your query:

federal-treasury-api@googlegroups.com

For any questions about the data, contact the Treasury:

http://www.treasury.gov/press-center/Press-Contacts/Pages/default.aspx

Credits:

The API was developed by csv soundsystem with the support of a Code Sprint Grant from the Knight-Mozilla OpenNews program. By Brian Abelson, Jake Bialer, Burton DeWilde, Michael Keller, Thomas Levine & Cezary Podkul. With an assist from Ashley Williams.

Find out more about csv soundsystem here: http://csvsoundsystem.com/

Find out more about OpenNews here: http://www.mozillaopennews.org/