Leveraging Open Data to Build a Blog Post

One of the unique features of FPWA’s Federal Funds Tracker (FFT) is that we ensured that all the data made available fulfilled the promise of open data and transparency, and to make analyzing City budget publications a seamless process.

In this blog post we will demonstrating how you can take full advantage of FFT’s Open Data by providing a walk-through of how to create a blog post leveraging the data that is featured on FFT. For this walk-through we will demonstrate how to create visualizations for current funding, historical context, President Trump’s FY 2020 budget proposal, and how to embed visualizations for an agency’s federal grants into a blog post for one of FPWA’s member organizations, Chinese American Planning Council (CPC).

For context, just as nonprofits in the City who provide these services lack sufficient resources to meet the needs of their communities and recruit and retain qualified staff as a result of City and State underinvestment, federal budget policy matters, too. Federal grants are often passed through from City agencies to nonprofit human-service providers.

For example, CPC relies on federal support to serve more than 60,000 individuals and families across Manhattan, Brooklyn, and Queens. CPC’s federal support would be cut by 44 percent ($2.5 million) under Trump’s budget, driven in part by the proposal to eliminate the Community Services Block Grants. CSBG – which has already fell by 14 percent since FY 2010 – supports a wide range of community-based activities to reduce poverty.

Follow the steps below to learn how to use FFT’s Open Data portal to create visualizations that showcases how federal disinvestment of grants have and will impact your organization and community.

1. Where to find the data
In addition to the Open Data tab, every chart featured on the Federal Funds Tracker has links in the footnotes that will take you to FPWA’s Tableau page or to a GitHub page where you can download, embed, and share the data.

After navigating to “Open Data”, click on “download the data sets”.

This will take you to a GitHub page where you can download multiple datasets. Navigate to Repositories and download the “Budget-Appendix”, “Inflation Adjuster”, and the “All-Federal-Grants-10to18-CAFR” spreadsheets.

2. Analyze
To use the “Budget-Appendix” spreadsheet, create a new column header in cell D1. Name it “CPC’s FY 2018 Federal Grants”. In cell E1 create another column header and name it “Trump $ Change”. In column D, we will then input CPC’s federal grants amount for FY 2018.

In cell E2, apply the Trump budget cuts by inputting the formula “=C2 * D2”. Copy that formula to cell D35.

Column E now shows how CPC’s federal grants will either reduced or increased based on Trump’s FY 2020 budget proposal. As you can see, because Senior Community Service Employment Program (SCSEP) is slated for elimination, this will cut CPC’s federal grants by $1.8 million.

With this analysis you can create visualizations to show the impact of Trump’s FY 2002 budget proposal.

Next, we will use the “Inflation Adjuster” and the “All-Federal-Grants-10to18-CAFR” spreadsheet to see how CPC’s federal grants have suffered from a decade of historical disinvestment and how Trump’s FY 2020 budget proposal would effectively be cuts on top of cuts.

Open the “All-Federal-Grants-10to18-CAFR” and navigate to the All Federal Grants tab, which is just a PivotTable of data in the CAFR 10-18 tab.

Use the PivotTable to find the federal grants your organization relies upon. After locating the federal grants, open the “Inflation Adjuster” spreadsheet and copy and paste the FY 2010 amount of the selected federal grant in cell C4. To complete the analysis, copy and paste the FY 2018 amount of the same selected federal grant in cell C9. You will now see in cell C14 how much the federal grant has either declined or increased since FY 2010 after adjusting for inflation.

As you can see Community Services Blog Grant (CSBG) has declined by $5 million since FY 2010 after adjusting for inflation.

3. Visualize and Embed

After creating visualizations from your analysis, you can also embed Tableau visualizations onto your digital media.

By clicking on the Tableau link, you will be taken to the same visualization you were viewing on the Federal Funds Tracker on a Tableau Workbook featured on FPWA’s Tableau Public page.

By clicking on the share icon you will be able to either Embed the Tableau Workbook to your own digital media or share the Tableau Workbook link.

Below is what an embedded Tableau workbook would look like:

Congratulations! You have successfully build a blog post that utilizes open data featured on FPWA’s Federal Funds Tracker.

To further show the impact of federal grants, include a story to show how your organization utilizes federal funding to provide essential services to your community.