Missing Financial Variables Across 2013-2019 CORE-PC HRMN Files

[corbinmwalls]

Hi,

Following up on issue #25 regarding missing total contributions in the 2019 file, I think I may have identified a broader version of the same issue affecting the CORE-PC files across the 2013-2019 range. I wanted to flag it here in case it is useful and to ask for guidance on how to handle it.

Computing percent non-missing by variable by year across the full panel, I find that a consistent set of summary financial variables shows near-complete missingness for 2014-2018, with partial coverage in 2013 (~67.8%) and 2019 (~41.4%), and full coverage in 2012 and 2020-2022. The data dictionary specifies 2012-2022 coverage for all of these variables, so the missingness appears to be unintended. The affected variables include:

• Part I/VIII revenue totals: F9_08_REV_TOT_TOT, F9_08_REV_CONTR_TOT, F9_08_REV_PROG_TOT_TOT, F9_08_REV_MISC_TOT_TOT, F9_08_REV_OTH_INVEST_INCOME_TOT, F9_08_REV_OTH_INV_NET_TOT, F9_08_REV_OTH_INV_GRO_SALE, F9_08_REV_OTH_INV_COST_GOODS, F9_08_REV_OTH_RENT_NET_TOT, F9_08_REV_OTH_SALE_GAIN_SEC, F9_08_REV_OTH_SALE_GAIN_OTH, F9_08_REV_OTH_SALE_ASSET_SEC, F9_08_REV_OTH_SALE_ASSET_OTH, F9_08_REV_OTH_SALE_LESS_COST_SEC, F9_08_REV_OTH_SALE_LESS_COST_OTH, F9_08_REV_MISC_TOT, F9_08_REV_MISC_TOT_2, F9_08_REV_MISC_TOT_3
• Part VII/IX expense totals: F9_09_EXP_TOT_TOT, F9_09_EXP_COMP_DTK_TOT, F9_09_EXP_FEE_SVC_FUNDR_TOT, F9_09_EXP_OTH_SAL_WAGE_TOT, F9_09_EXP_PAYROLL_TAX_TOT, F9_09_EXP_OTH_OTH_TOT, F9_07_COMP_DTK_COMP_ORG_TOT, F9_07_COMP_DTK_COMP_OTH_TOT, F9_07_COMP_DTK_COMP_RLTD_TOT
• Part X balance sheet totals: F9_10_ASSET_TOT_EOY, F9_10_LIAB_TOT_EOY, F9_10_NAFB_TOT_EOY, F9_10_LIAB_MTG_NOTE_EOY, F9_10_LIAB_TAX_EXEMPT_BOND_EOY, F9_10_NAFB_EARNING_RETAINED_EOY, F9_10_ASSET_LAND_BLDG_DEPREC

The exact year-by-year percent non-missing for a representative variable (F9_10_ASSET_TOT_EOY) is:

• 2012: 99.8%
• 2013: 67.8%
• 2014: 0.13%
• 2015: 0.28%
• 2016: 0.70%
• 2017: 2.13%
• 2018: 8.85%
• 2019: 41.4%
• 2020: 100%
• 2021: 100%
• 2022: 100%

These figures are from a study-specific panel restricted to human services organizations, so the exact percentages may vary slightly from the full CORE-PC files, though the pattern holds. All other affected variables follow this exact same pattern. The component line items—individual balance sheet lines, individual expense lines, individual revenue lines—are fully populated in all years, which suggests the issue is specific to the summary totals rather than the underlying source data.

Questions:

1. Is this a known issue, and if so is there a corrected version of the 2013-2019 files available?
2. If not, would supplementing with the IRS SOI extracts for those years be the appropriate workaround, as suggested in issue What is variable for Total Contributions for CORE2019 PC FULL 990 #25?
3. For cases where the summary totals are missing but the detailed component variables are available, would it be reasonable to reconstruct the totals by summing the components? I want to be careful here since some of the component variables may themselves have missingness in certain cases, which could lead to understated totals rather than true NAs. I am also not sure all the component variables are available in every year.
4. Are there other approaches you would recommend for handling this in a longitudinal study spanning 2012-2022?

Thank you so much for your work maintaining these files @lecy and @Thiyaghessan. They are an invaluable resource for nonprofit research and I really appreciate your responsiveness on issues like this.

[lecy]

Just confirming that these are issues with the CORE panel, but not with the equivalent efile datasets, correct? You are acquiring files through the CORE downloads page, or through the dashboard tool?

I have built a harmonization process for the SOI extracts to replicate the structure of the CORE. The files start around 2011/2012 and go through 2022 with partial 2023 and 2024 years. You can inspect them here (just change the year for different files):

• https://nccsdata.s3.us-east-1.amazonaws.com/raw/soi/processed_plus_bmf/pz_taxyear_2014.csv
• https://nccsdata.s3.us-east-1.amazonaws.com/raw/soi/processed_plus_bmf/SOI-DATA-PANEL.xlsx
• https://nccsdata.s3.us-east-1.amazonaws.com/raw/soi/processed_plus_bmf/PZ-DATA-DICTIONARY.R

Do you see similar issues with those variables?

[corbinmwalls]

Hi @lecy,

I am using the CORE downloads page rather than the dashboard tool, and I have not looked at whether this issue is present in the efile datasets.

The harmonized SOI files are a huge help. Thank you so much for pointing me to them. After filtering to FTYPE == "990-PC", a quick check on just 2014 suggests that the SOI files have 100% coverage on the core variables that were missing in the NCCS CORE files, including:

• F9_08_REV_TOT_TOT
• F9_09_EXP_TOT_TOT
• F9_10_ASSET_TOT_EOY
• F9_10_LIAB_TOT_EOY
• F9_10_NAFB_TOT_EOY
• F9_08_REV_CONTR_TOT
• F9_08_REV_PROG_TOT_TOT
• F9_08_REV_OTH_INVEST_INCOME_TOT

This resolves the most critical gaps for my analysis. That said, a number of the more detailed variables from my original list are not present in the harmonized SOI files, as I'm sure you know. These include:

• Expense detail variables: F9_09_EXP_COMP_DTK_TOT, F9_09_EXP_FEE_SVC_FUNDR_TOT, F9_09_EXP_OTH_SAL_WAGE_TOT, F9_09_EXP_PAYROLL_TAX_TOT, F9_07_COMP_DTK_COMP_ORG_TOT, F9_07_COMP_DTK_COMP_OTH_TOT, F9_07_COMP_DTK_COMP_RLTD_TOT
• Balance sheet details: F9_10_LIAB_MTG_NOTE_EOY, F9_10_LIAB_TAX_EXEMPT_BOND_EOY, F9_10_NAFB_EARNING_RETAINED_EOY, F9_10_ASSET_LAND_BLDG_DEPREC
• Revenue detail variables: F9_08_REV_OTH_SALE_GAIN_SEC, F9_08_REV_OTH_SALE_GAIN_OTH, F9_08_REV_OTH_RENT_NET_TOT, F9_08_REV_MISC_TOT, F9_08_REV_MISC_TOT_2, F9_08_REV_MISC_TOT_3

I am able to move forward with the research project despite these limitations, so I thank you very much for your guidance! However, these additional variables could be useful for other research projects, of course.

As I am sure you know, the 2014 harmonized SOI file has a small number of duplicate column names. I confirmed the duplicates are identical so simply dropping one copy works fine, but wanted to flag it in case it is an unintended artifact of the harmonization process.

Thanks again for your help and for maintaining these resources!

[lecy]

Try:

• https://nccsdata.s3.us-east-1.amazonaws.com/raw/soi/processed_plus_bmf/pc_taxyear_2014.csv
• https://nccsdata.s3.us-east-1.amazonaws.com/raw/soi/processed_plus_bmf/PC-DATA-DICTIONARY.R

The PC (full 990) version of the data has more variables but less coverage (excludes 990EZ filers, fewer rows).

[lecy]

As I am sure you know, the 2014 harmonized SOI file has a small number of duplicate column names. I confirmed the duplicates are identical so simply dropping one copy works fine, but wanted to flag it in case it is an unintended artifact of the harmonization process.

Thanks for flagging! I did a spot check as well, and the two columns are identical. I'll have to check the code to figure out where the duplication occurs.

Also just noting that the SOI extracts are helpful from about 2012-2021, but from that point forward the efile panel is the best source.

# % of records by source type

|     | EFILE| PAPER|
|:----|-----:|-----:|
|2010 |  0.00|  1.00|
|2011 |  0.00|  1.00|
|2012 |  0.05|  0.95|
|2013 |  0.48|  0.52|
|2014 |  0.63|  0.37|
|2015 |  0.66|  0.34|
|2016 |  0.69|  0.31|
|2017 |  0.72|  0.28|
|2018 |  0.75|  0.25|
|2019 |  0.80|  0.20|
|2020 |  0.90|  0.10|
|2021 |  0.99|  0.01|
|2022 |  1.00|  0.00|
|2023 |  1.00|  0.00|
|2024 |  1.00|  0.00|