U.S. Treasury FiscalData API in C#
By Cyrus Gomes
U.S. Department of the Treasury FiscalData API Documentation: https://fiscaldata.treasury.gov/api-documentation/
FiscalData Dataset Search: https://fiscaldata.treasury.gov/datasets/
This is a useful tool for finding endpoints and parameters for this API.
Terms of Use: https://fiscaldata.treasury.gov/api-documentation/#license-and-authorization
To get started, import the libraries that will be used in this tutorial:
Setup#
First, install the CURL and jq packages by typing the following command in the terminal:
!sudo apt install curl jq
Now we set a directory where we want the US-Treasury directory for our projects to be created:
!mkdir US-Treasury
Finally, we change to our newly created directory:
%cd US-Treasury
1. Treasury Reporting Rates of Exchange#
This first example will use the /rates_of_exchange/ endpoint. First, let’s assemble our url for API request. This request is made up of three parts:
Base url: The URL for the U.S. Treasury Fiscal Data API. All requests to this API must start withs this URL.
Endpoint: The specific dataset that we wish to retrieve from this API. In this example, it is
/rates_of_exchange/Parameters: Further specifications that we request from the endpoint. In this case, we are requesting a few fields with a filter on the date:
Fields: The specific data points that we are requesting from the endpoint. In this example, we retrieve the country, currency, and exchange rate.
Filters: Limits on the data received from the endpoint. In this example, we limit the data to the data points recorded on Dec. 31, 2022. We can set the page size to 1000, since the default for this API is to return the first 100 results. This allows us to retrieve the full dataset.
More information on parameters for this API can be found here. Information on the parameters for this specific endpoint can be found here.
First, we can initialize and change to a directory for all the project files:
!mkdir exchange_rates
%cd exchange_rates
We utilize the %%file command to create the following makefile which will compile our program and create an executable:
%%file makefile
# Set the variable CC to gcc, which is used to build the program
CC=gcc
# Enable debugging information and enable all compiler warnings
CFLAGS=-g -Wall
# Set the bin variable as the name of the binary file we are creating
BIN=treasury_data
# Create the binary file with the name we put
all: $(BIN)
# Map any file ending in .c to a binary executable.
# "$<" represents the .c file and "$@" represents the target binary executable
%: %.c
# Compile the .c file using the gcc compiler with the CFLAGS and links
# resulting binary with the CURL library
$(CC) $(CFLAGS) $< -o $@ -lcurl
# Clean target which removes specific files
clean:
# Remove the binary file and an ".dSYM" (debug symbols for debugging) directories
# the RM command used -r to remove directories and -f to force delete
$(RM) -rf $(BIN) *.dSYM
Overwriting makefile
This command is used again to create our .c file which contains the code for the program:
%%file treasury_data.c
#include <curl/curl.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* CURL program that retrieves treasury data from
https://api.fiscaldata.treasury.gov/services/api/fiscal_service/ */
int main (int argc, char* argv[]) {
// If the arguments are invalid then return
if (argc < 2) {
printf("Error. Please try again correctly. (./treasury_data -e [endpoint] -p [parameter])\n");
return -1;
}
// Initialize the CURL HTTP connection
CURL *curl = curl_easy_init();
// Bits of the URL that are joined together later
char api[] = "https://api.fiscaldata.treasury.gov/services/api/fiscal_service/";
char url[1000];
char parameters[] = "?fields=country,currency,exchange_rate&filter=record_date:eq:2022-12-31&page[size]=1000";
char endpoint[] = "v1/accounting/od/rates_of_exchange";
// Check if CURL initialization successful
if (!curl) {
fprintf(stderr, "init failed\n");
return EXIT_FAILURE;
}
/* Here are different ways of calling the program in the
command line and integrating endpoint and parameter fields.*/
// Has the -e flag: /treasury_data -e
if ((argc==2) && (strcmp(argv[1],"-e")==0)) {
// Combine the API and default endpoint to produce a functioning URL
sprintf(url, "%s%s%s", api, endpoint, parameters);
}
// Has the -e flag and field: ./treasury_data -e [e]
else if ((argc==3) && (strcmp(argv[1],"-e")==0)) {
// Combine the API and custom endpoint
sprintf(url, "%s%s%s", api, argv[2], parameters);
}
// Has the -e and -p flags: ./treasury_data -p -e
else if ((argc==3) && (strcmp(argv[1],"-p")==0) && (strcmp(argv[2],"-e")==0)) {
// Combine the API and default endpoint
sprintf(url, "%s%s%s", api, endpoint, parameters);
}
// Has the -e and -p flags and the parameter field: ./treasury_data -e -p [parameter]
else if ((argc==4) && (strcmp(argv[2],"-p")==0) && (strcmp(argv[1],"-e")==0)) {
// Combine the API, default endpoint, and parameter to produce a functioning URL
sprintf(url, "%s%s%s", api, endpoint, argv[3]);
}
// Has the -e and -p flags and the endpoint field: ./treasury_data -e [e] -p
else if ((argc==4) && (strcmp(argv[3],"-p")==0) && (strcmp(argv[1],"-e")==0)) {
// Combine the API and custom endpoint
sprintf(url, "%s%s%s", api, argv[2], parameters);
}
// Has the -p and -e flags and the parameter field: ./treasury_data -p -e [e]
else if ((argc==4) && (strcmp(argv[1],"-p")==0) && (strcmp(argv[2],"-e")==0)) {
// Combine the API and custom endpoint
sprintf(url, "%s%s%s", api, argv[3], parameters);
}
// Has the -p and -e flags and the parameter and endpoint field: ./treasury_data -p [parameter] -e [e]
else if ((argc==5) && (strcmp(argv[1],"-p")==0) && (strcmp(argv[3],"-e")==0)) {
// Combine the API, custom endpoint, and parameter to produce the URL
sprintf(url, "%s%s%s", api, argv[4], argv[2]);
}
// Has the -e and -p flags and the endpoint and parameter field: ./treasury_data -e [e] -p [parameter]
else if ((argc==5) && (strcmp(argv[3],"-p")==0)) {
// Combine the API, custom endpoint, and parameter to produce the URL
sprintf(url, "%s%s%s", api, argv[2], argv[4]);
}
// If the arguments are invalid then return
else {
printf("./treasury_data -p [parameter] -e [e]\n");
curl_easy_cleanup(curl);
return 0;
}
// Set the URL to which the HTTP request will be sent to
// First parameter is for the initialized curl HTTP request, second for the option to be set, and third for the value to be set
curl_easy_setopt(curl, CURLOPT_URL, url);
// If result is not retrieved then output error
CURLcode result = curl_easy_perform(curl);
// If result is not retrieved then output error
if (result != CURLE_OK) {
fprintf(stderr, "download problem: %s\n", curl_easy_strerror(result));
}
// Deallocate memory for the CURL connection
curl_easy_cleanup(curl);
return EXIT_SUCCESS;
}
Writing treasury_data.c
!make
gcc -g -Wall treasury_data.c -o treasury_data -lcurl
We can run the program to get the number of entries:
!./treasury_data -p -e | jq '.["data"] | length'
180
# Display first 3 results
!./treasury_data -p -e | jq '.["data"][:3]'
[
{
"country": "Afghanistan",
"currency": "Afghani",
"exchange_rate": "89.11"
},
{
"country": "Albania",
"currency": "Lek",
"exchange_rate": "106.5"
},
{
"country": "Algeria",
"currency": "Dinar",
"exchange_rate": "136.467"
}
]
%%bash
# Save the output data to a JSON format
exchange_rate_data=$(./treasury_data -p -e | jq -r '[.["data"][] | {country, currency, exchange_rate: (.exchange_rate | tonumber)}]')
# Save the JSON data to a file
echo "${exchange_rate_data}" > exchange_rate_data.json
# Print the first 10 lines of the json data
cat exchange_rate_data.json | head -n 10
[
{
"country": "Afghanistan",
"currency": "Afghani",
"exchange_rate": 89.11
},
{
"country": "Albania",
"currency": "Lek",
"exchange_rate": 106.5
Now, we can save this data to a TSV file:
%%bash
# Display the formatted output without field names
jq -r '.[] | [.country, .currency, .exchange_rate] | @tsv' exchange_rate_data.json > exchange_rate_data.tsv
# Output the first 10 lines of the tsv file
cat exchange_rate_data.tsv | head -n 10
Afghanistan Afghani 89.11
Albania Lek 106.5
Algeria Dinar 136.467
Angola Kwanza 503.65
Antigua & Barbuda E. Caribbean Dollar 2.7
Argentina Peso 183
Argentina Peso 203.5
Armenia Dram 400
Australia Dollar 1.471
Azerbaijan Manat 1.7
2. Debt to the Penny#
This example will use the /debt_to_penny/ endpoint. Once again, our API request is made up of 3 parts:
Base url: The URL for the U.S. Treasury Fiscal Data API. All requests to this API must start withs this URL.
Endpoint: The specific dataset that we wish to retrieve from this API. In this example, it is
/debt_to_penny/Parameters: Further specifications that we request from the endpoint. In this case, we are requesting a few fields with a filter on the date:
Fields: The specific data points that we are requesting from the endpoint. In this example, we retrieve the total public debt for each date.
Filters: Limits on the data received from the endpoint. In this example, we limit the data to the data points recorded on dates greater than or equal to (gte) Jan. 1, 2012 and less than Jan. 1, 2023. We can also set the page size to 10000, since the default for this API is to return the first 100 results. This allows us to retrieve the full dataset.
More information on parameters for this API can be found here. Information on the parameters for this specific endpoint can be found here.
!./treasury_data -e "v2/accounting/od/debt_to_penny" -p "?fields=record_date,tot_pub_debt_out_amt&filter=record_date:gte:2012-01-01,record_date:lt:2023-01-01&page[size]=10000" | jq '.["data"] | length'
2763
# Display first 5 results
!./treasury_data -e "v2/accounting/od/debt_to_penny" -p "?fields=record_date,tot_pub_debt_out_amt&filter=record_date:gte:2012-01-01,record_date:lt:2023-01-01&page[size]=10000" | jq '.["data"][:5]'
[
{
"record_date": "2012-01-03",
"tot_pub_debt_out_amt": "15226217488652.33"
},
{
"record_date": "2012-01-04",
"tot_pub_debt_out_amt": "15236542247490.89"
},
{
"record_date": "2012-01-05",
"tot_pub_debt_out_amt": "15236541899973.10"
},
{
"record_date": "2012-01-06",
"tot_pub_debt_out_amt": "15236532960597.70"
},
{
"record_date": "2012-01-09",
"tot_pub_debt_out_amt": "15236506139986.86"
}
]
Now, we’ll find the total public debt reported in the first recorded date of each month in the dataset:
%%bash
# This algorithm will store the first total debt per month (first total debt
# recorded in the month) and create a TSV with year, month, day, total debt
data=$(./treasury_data -e "v2/accounting/od/debt_to_penny" -p "?fields=record_date,tot_pub_debt_out_amt&filter=record_date:gte:2012-01-01,record_date:lt:2023-01-01&page[size]=10000" | jq '.["data"]')
# Use jq to filter the first tot_pub_debt_out_amt in the month and store the month and year
filtered_data=$(echo "$data" | jq -r 'group_by(.record_date | split("-")[:2] | join("-")) |
map({year_month: .[0].record_date | split("-")[:2] | join("-"), tot_pub_debt_out_amt: .[0].tot_pub_debt_out_amt | tonumber})')
# Store the data in a TSV format
tsv_data=$(echo "$filtered_data" | jq -r '.[] | "\(.year_month | split("-")[1])\t\(.year_month | split("-")[0])\t\(.tot_pub_debt_out_amt)"')
# Output the tsv_data to tot_debt.tsv file
echo "$tsv_data" > tot_debt.tsv
# Print the first 10 lines of the tsv file
cat tot_debt.tsv | head -n 10
01 2012 15226217488652.33
02 2012 15330778119850.6
03 2012 15501014716143.72
04 2012 15620325998403.96
05 2012 15673229738379.98
06 2012 15724907364995.04
07 2012 15888741858820.66
08 2012 15907138660280.97
09 2012 16008060301079.15
10 2012 16159487013300.35
4. Income Tax Refunds Issued#
This example will use the /income_tax_refunds_issued/ endpoint, which is an extension of the /dts/ (daily treasury statement) endpoint. Once again, our API request is made up of 3 parts:
Base url: The URL for the U.S. Treasury Fiscal Data API. All requests to this API must start withs this URL.
Endpoint: The specific dataset that we wish to retrieve from this API. In this example, it is
/income_tax_refunds_issued/Parameters: Further specifications that we request from the endpoint. In this case, we are requesting a few fields with a filter on the date:
Fields: The specific data points that we are requesting from the endpoint. In this example, we retrieve the tax refund types, descriptions, and amounts rounded to the newarest million for each date.
Filters: Limits on the data received from the endpoint. In this example, we limit the data to the data points recorded on dates greater than or equal to (gte) Jan. 1, 2022 and less than Jan. 1, 2023. We also limit the page size to 10000, since the default for this API is to return the first 100 results. This allows us to retrieve the full dataset.
More information on parameters for this API can be found here. Information on the parameters for this specific endpoint can be found here.
!./treasury_data -e "/v1/accounting/dts/income_tax_refunds_issued" -p "?fields=record_date,tax_refund_type,tax_refund_type_desc,tax_refund_today_amt&filter=record_date:gte:2022-01-01,record_date:lt:2023-01-01&page[size]=10000" | jq '.["data"] | length'
1756
# Display first 5 results
!./treasury_data -e "/v1/accounting/dts/income_tax_refunds_issued" -p "?fields=record_date,tax_refund_type,tax_refund_type_desc,tax_refund_today_amt&filter=record_date:gte:2022-01-01,record_date:lt:2023-01-01&page[size]=10000" | jq '.["data"][:5]'
[
{
"record_date": "2022-01-03",
"tax_refund_type": "IRS - Advanced Child Tax Credit (Checks)",
"tax_refund_type_desc": "null",
"tax_refund_today_amt": "0"
},
{
"record_date": "2022-01-03",
"tax_refund_type": "IRS - Advanced Child Tax Credit (EFT)",
"tax_refund_type_desc": "null",
"tax_refund_today_amt": "0"
},
{
"record_date": "2022-01-03",
"tax_refund_type": "IRS - Economic Impact Payments (Checks)",
"tax_refund_type_desc": "null",
"tax_refund_today_amt": "0"
},
{
"record_date": "2022-01-03",
"tax_refund_type": "IRS - Economic Impact Payments (EFT)",
"tax_refund_type_desc": "null",
"tax_refund_today_amt": "0"
},
{
"record_date": "2022-01-03",
"tax_refund_type": "IRS Tax Refunds Business",
"tax_refund_type_desc": "Checks",
"tax_refund_today_amt": "0"
}
]
We can store values in (date, refund type, refund type description, refund amount) and save it to a TSV file:
%%bash
# This algorithm will be used to output values in (date, refund type, refund type description, refund amount)
# Make API call to store data to variable
data=$(./treasury_data -e "/v1/accounting/dts/income_tax_refunds_issued" -p "?fields=record_date,tax_refund_type,tax_refund_type_desc,tax_refund_today_amt&filter=record_date:gte:2022-01-01,record_date:lt:2023-01-01&page[size]=10000" | jq '.["data"]')
# Store the data in a TSV format
tsv_data=$(echo "$data" | jq -r '.[] | "\(.record_date)\t\(.tax_refund_type)\t\(.tax_refund_type_desc)\t\(.tax_refund_today_amt)"')
# Output the tsv_data to tax_refunds_by_day.tsv file
echo "$tsv_data" > tax_refunds_by_day.tsv
# Print the first 10 lines of the TSV file
cat tax_refunds_by_day.tsv | head -n 10 | column -t -s $'\t'
2022-01-03 IRS - Advanced Child Tax Credit (Checks) null 0
2022-01-03 IRS - Advanced Child Tax Credit (EFT) null 0
2022-01-03 IRS - Economic Impact Payments (Checks) null 0
2022-01-03 IRS - Economic Impact Payments (EFT) null 0
2022-01-03 IRS Tax Refunds Business Checks 0
2022-01-03 IRS Tax Refunds Business EFT 0
2022-01-03 IRS Tax Refunds Individual Checks 249
2022-01-03 IRS Tax Refunds Individual EFT 12
2022-01-04 IRS - Advanced Child Tax Credit (Checks) null 0
2022-01-04 IRS - Advanced Child Tax Credit (EFT) null 0
3. Social Security Administration Data#
For the next example, we’ll look at the
/statement_net_cost/endpoint:We can utilize the following Bash script to output the first
gross_cost_bil_amtandearned_revenue_bil_amtin every year and save it to a TSV file: