These instructions will help you better analyze the IRS 990 public dataset. The first thing you'll want to do is to read through the documentation over at Amazon. There's a ~108MB index file called index.json.gz that contains metadata describing the entire corpus.
To download the index.json.gz metadata file, you'll want to issue the following command: curl https://s3.amazonaws.com/irs-form-990/index.json.gz. Once you've downloaded the index.json.gz file, you can extract its contents with the following command: gunzip index.json.gz. To take a peek at the extracted contents, use the following command: head index.json.
Looking at the index.json file, you'll notice that it contains a json structure represented as a string. It contains an array of json objects that look like the following:
{"EIN": "721221647", "SubmittedOn": "2016-02-05", "TaxPeriod": "201412", "DLN": "93493309001115", "LastUpdated": "2016-03-21T17:23:53", "URL": "https://s3.amazonaws.com/irs-form-990/201513099349300111_public.xml", "FormType": "990", "ObjectId": "201513099349300111", "OrganizationName": "FERBER FAMILY OF HOUMA FNDTN CO JEWISH ENDOWMENT FOUNDATION", "IsElectronic": true, "IsAvailable": true}Each of these records represents if an electronic 990 filing is available for an organization. If an organization's electronic 990 filing is not available, you'll notice that "IsAvailable" will be set to false and it will be missing a "URL" entry.
Once you've figured out what entries you'd like to look at, please follow the below instructions to install tools that can help you further analyze the data.
As a note, you don't need to download the entire corpus to perform your analysis nor do you need an Amazon account.
Pull the business name, person name, title, hours_per_week, and total comp from the Form990PartVIISectionAGrp for a single curl call
curl -s https://s3.amazonaws.com/irs-form-990/201541349349307794_public.xml | xml2json | jq -c '
.Return.ReturnHeader.Filer.BusinessName.BusinessNameLine1Txt."$t" as
$companyName | .Return.ReturnData.IRS990.Form990PartVIISectionAGrp | .[] |
{company: $companyName, name:.PersonNm."$t", title: .TitleTxt."$t", hours_per_week: .AverageHoursPerWeekRt."$t",
total_comp: ((.ReportableCompFromOrgAmt."$t"|tonumber) + (.ReportableCompFromRltdOrgAmt."$t"|tonumber) +
(.OtherCompensationAmt."$t"|tonumber))}'Create csv friendly output including the business name, person name, title, hours_per_week, and total comp from the Form990PartVIISectionAGrp
for f in *.xml; do xml2json < $f | jq -r '
.Return.ReturnHeader.Filer.BusinessName.BusinessNameLine1Txt."$t" as
$companyName | .Return.ReturnData.IRS990.Form990PartVIISectionAGrp | map([$companyName, .PersonNm."$t", .TitleTxt."$t",
(.AverageHoursPerWeekRt."$t"|tonumber), ((.ReportableCompFromOrgAmt."$t"|tonumber) +
(.ReportableCompFromRltdOrgAmt."$t"|tonumber) + (.OtherCompensationAmt."$t"|tonumber))]) | .[] | @csv'; doneCreate a csv file called "990.csv" that includes the business name, person name, title, hours_per_week, and total comp from the Form990PartVIISectionAGrp
for f in *.xml; do xml2json < $f | jq -r '
.Return.ReturnHeader.Filer.BusinessName.BusinessNameLine1Txt."$t" as
$companyName | .Return.ReturnData.IRS990.Form990PartVIISectionAGrp | map([$companyName, .PersonNm."$t", .TitleTxt."$t",
(.AverageHoursPerWeekRt."$t"|tonumber), ((.ReportableCompFromOrgAmt."$t"|tonumber) +
(.ReportableCompFromRltdOrgAmt."$t"|tonumber) + (.OtherCompensationAmt."$t"|tonumber))]) | .[] | @csv'; done > 990.csvPull the business name, person name, title, hours_per_week, and total comp from the Form990PartVIISectionA for a single curl call
curl -s https://s3.amazonaws.com/irs-form-990/201302899349300950_public.xml | xml2json | jq -c '
.Return.ReturnHeader.Filer.Name.BusinessNameLine1."$t" as
$companyName | .Return.ReturnData.IRS990.Form990PartVIISectionA | .[] |
{company: $companyName, name:.NamePerson."$t", title: .Title."$t", hours_per_week: .AverageHoursPerWeek."$t"|tonumber,
total_comp: ((.ReportableCompFromOrganization."$t"|tonumber) + (.ReportableCompFromRelatedOrgs."$t"|tonumber) +
(.OtherCompensation."$t"|tonumber))}'Pull the business name, person name, title, hours_per_week, and total comp from the OfficerDirectorTrusteeEmplGrp for a single curl call
curl -s https://s3.amazonaws.com/irs-form-990/201502469349200225_public.xml | xml2json | jq -c '
.Return.ReturnHeader.Filer.BusinessName.BusinessNameLine1Txt."$t" as
$companyName | .Return.ReturnData.IRS990EZ.OfficerDirectorTrusteeEmplGrp | .[] |
{company: $companyName, name:.PersonNm."$t", title: .TitleTxt."$t", hours_per_week: .AverageHrsPerWkDevotedToPosRt."$t"|tonumber,
compensation_amount: (.CompensationAmt."$t"|tonumber)}'Create csv friendly output including the business name, person name, title, hours_per_week, and total comp from the OfficerDirectorTrusteeEmplGrp
for f in *.xml; do xml2json < $f | jq -r '
.Return.ReturnHeader.Filer.BusinessName.BusinessNameLine1Txt."$t" as
$companyName | .Return.ReturnData.IRS990EZ.OfficerDirectorTrusteeEmplGrp | map([$companyName, .PersonNm."$t", .TitleTxt."$t", (.AverageHrsPerWkDevotedToPosRt."$t"|tonumber), (.CompensationAmt."$t"|tonumber)]) | .[] | @csv'; doneCreate a csv file called "990EZ.csv" that includes the business name, person name, title, hours_per_week, and total comp from the OfficerDirectorTrusteeEmplGrp
for f in *.xml; do xml2json < $f | jq -r '
.Return.ReturnHeader.Filer.BusinessName.BusinessNameLine1Txt."$t" as
$companyName | .Return.ReturnData.IRS990EZ.OfficerDirectorTrusteeEmplGrp | map([$companyName, .PersonNm."$t", .TitleTxt."$t", (.AverageHrsPerWkDevotedToPosRt."$t"|tonumber), (.CompensationAmt."$t"|tonumber)]) | .[] | @csv'; done > 990EZ.csv
Ryan:
¶ Interesting. With respect to the rules and background for this data, the IRS publishes a huge amount of detailed instructions, and they are well worth reading (or at least searching) to find some of the reasoning. Be careful because the rules sometimes change from year-to-year. For the example return you cited, https://s3.amazonaws.com/irs-form-990/201541349349307794_public.xml, Tax Year 2014 instructions are https://www.irs.gov/pub/irs-prior/i990--2014.pdf and Schedule J is https://www.irs.gov/pub/irs-prior/i990--2014.pdf.
I haven't run across cases where individuals were omitted from Sched. J but are in Part VII (or have forgotten it!), though glancing at the main form instructions, that would appear to be pp.26-27:
My logic in suggesting using the aggregated number is that this saves you having to carefully read the rules and understand how compensation is broken down, and if there's a change in the number of fields that are added to reach the total (unlikely!), your numbers would change.
On the other hand, there's also a good argument that the total compensation includes "other reportable compensation" which is sometimes goofy. In particular it can include deferred compensation packages associated with retirement and if you quote that number, you run the risk of people saying "That's not my salary! That's not what's on my W2!". So it does pay to understand the component numbers.
¶ Sorry, I was unclear. I just think it's important to not imply there might be a barrier to entry of having to download gigabytes of data. Already there is confusion over whether the data is publicly accessible (e.g. some people seemed to think that you needed an AWS account to access it, and didn't understand it was available over HTTP as well). I wouldn't want someone to read this and conclude "Oh, I need to download ALL the data in order to do anything with it?" Of course there's plenty of interesting stuff that can only be done with the entire corpus of data.
Though if you're running this on the entire corpus of all returns, performance starts to matter. I would think that XSLT would be faster than converting from XML to JSON and then using 'jq', though definitely that's premature optimization :)