{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Intro","EIN","Endowments","Compensation","Tuition"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Intro","index":0,"n_rows":41,"n_cols":3,"rows_kept":41,"cut":false,"hidden":false,"merged":[]}
["This workbook imports Form 990 XML data and enables creation of variables for further analysis."]
[]
["Software Requirements:"]
[null,"You need Microsoft Excel, Windows version 2007 or greater to get full functionality from this file."]
[null,null,"The file can be opened with Mac Excel, but it uses Visual Basic code that may not be fully supported on a Mac. "]
[]
[null,"You must be sure to enable macros or the buttons and scroll bars in this macro-enabled workbook will not work."]
[null,"If you did not do so, please close the workbook and open it again, enabling macros."]
[null,"Search \"enable macros\" in Excel's Help for more information on how to do this."]
[]
["To use this file:"]
[null,"1) Click the Get CSV button in the EIN sheet to import a list of all Form 990s filed in a given year."]
[null,null,"Form 990s are constantly added as institutions file; most colleges get extensions and file in May or August for the previous fiscal (7/1 to 6/30) year. "]
[null,"2) Provide employer identification numbers (EIN) starting cell A2 of the EIN sheet."]
[null,"3) Click the Get XML button in the EIN sheet to download Form 990s into this workbook. "]
[null,null,"Click OK as Excel displays messages (e.g., \"No XML Schema\") and read the error logs after each organization's 990 is downloaded."]
[null,"4) Return to the EIN sheet after the data are downloaded for further analysis (via the buttons or user-created formulas and code)."]
[null,null,"The Endow, Comp, and Tuition buttons display data pulled from the individual Form 990 XML sheets in a single sheet."]
[null,null,"They contain a listbox in cell B1 that can be used to investigate data problems by taking you to the location of the data in an individual Form 990 XML sheet."]
[null,"Usage Notes:"]
[null,null,"Since the index_xxxx sheet is quite large and it is not needed after the XML data are downloaded, it can be deleted if file size is an issue."]
[null,null,"The individual Form 990 sheets can also be safely deleted (hold down the shift key to select multiple sheets) if needed."]
[]
["Amazon S3 and XML source documentation is here:"]
["https://aws.amazon.com/public-datasets/irs-990/"]
[]
["Form 990 Instructions are here:"]
["https://www.irs.gov/pub/irs-pdf/i990.pdf"]
[]
["Logic and Purpose of this workbook:"]
[null,"Given an EIN, look up the OBJECT_ID in the index_xxxx sheet to create a URL to the Form 990 XML file for a particular organization."]
[null,"Import the Form 990 XML file for each organization into its own worksheet in this workbook."]
[null,"All code is open source and can be accessed by pressing Alt-F11 to see the Visual Basic modules in this workbook."]
[null,"Once the data are downloaded, they can be analyzed using the buttons in the EIN sheet or the user's own manipulations and computations."]
[]
["All questions, comments, and suggestions welcome."]
["Humberto Barreto"]
["hbarreto@depauw.edu"]
["Michele Villinski"]
["mvillinski@depauw.edu"]
["2017-08-11"]

{"sheet":"EIN","index":1,"n_rows":24,"n_cols":6,"rows_kept":24,"cut":false,"hidden":false,"merged":[]}
["EIN","TAXPAYER NAME",null,"Please follow these steps in order:"]
[null,null,null,"1) Click the Get CSV button to import a CSV file from Amazon S3 into a new sheet that has a listing of taxpayers for that year."]
[null,null,null,2014]
[]
[]
[null,null,null,"2) Enter EINs starting in cell A2: search or scroll (sorted by insitution name) in the index_xxxx sheet dowloaded in Step 1."]
[]
[null,null,null,"3) Click the Get XML button to download Form 990s for the EINs in column A."]
[null,null,null,null,"Click OK as Excel displays messages (e.g., \"No XML Schema\") and read the error logs after each organization's 990 is downloaded."]
[]
[]
[null,null,null,"4) After data are downloaded, use these buttons for further analysis."]
[null,null,null,null,"Each button pulls a few variables from each taxpayer's Form 990 sheet."]
[null,null,null,null,"Comments (marked by a little red triangle like in this cell) have been added to those cells in the taxpayer's Form 990 that were selected for analysis. "]
[null,null,null,null,"To examine individual observations and correct data errors, do this from the Endowments, Compensation, or Tuition sheets:"]
[null,null,null,null,null,"1) Select a cell with a variable name and then "]
[null,null,null,null,null,"2) Select a taxpayer from the dropdown list box."]
[null,null,null,null,"This will take you to that cell in that taxpayer's Form 990 sheet."]
[]
[]
[]
[null,null,null,"After the buttons above are used, you may safely delete the index_xxxx sheet (it's big) if file size is an issue (e.g., slow calculation)."]
[null,null,null,"You may also delete the individual Form 990 sheets (hold down the shift key to delete multiple sheets at once) if they"]
[null,null,null,"are no longer needed."]

{"sheet":"Endowments","index":2,"n_rows":4,"n_cols":42,"rows_kept":4,"cut":false,"hidden":true,"merged":[]}
["Explore data -->",2,"ns1:BeginningYearBalanceAmt","ns1:ContributionsAmt","ns1:InvestmentEarningsOrLossesAmt","ns1:GrantsOrScholarshipsAmt","ns1:OtherExpendituresAmt","ns1:AdministrativeExpensesAmt","ns1:EndYearBalanceAmt","ns1:BeginningYearBalanceAmtxxx","ns1:ContributionsAmtxxx","ns1:InvestmentEarningsOrLossesAmtxxx","ns1:GrantsOrScholarshipsAmtxxx","ns1:OtherExpendituresAmtxxx","ns1:AdministrativeExpensesAmtxxx","ns1:EndYearBalanceAmtxxx","ns1:BeginningYearBalanceAMTxxx","ns1:ContributionsAMTxxx","ns1:InvestmentEarningsOrLossesAMTxxx","ns1:GrantsOrScholarshipsAMTxxx","ns1:OtherExpendituresAMTxxx","ns1:AdministrativeExpensesAMTxxx","ns1:EndYearBalanceAMTxxx","ns1:BeginningYearBalanceAMTxxx","ns1:ContributionsAMTxxx","ns1:InvestmentEarningsOrLossesAMTxxx","ns1:GrantsOrScholarshipsAMTxxx","ns1:OtherExpendituresAMTxxx","ns1:AdministrativeExpensesAMTxxx","ns1:EndYearBalanceAMTxxx","ns1:BeginningYearBalanceAMTxxx","ns1:ContributionsAMTxxx","ns1:InvestmentEarningsOrLossesAMTxxx","ns1:GrantsOrScholarshipsAMTxxx","ns1:OtherExpendituresAMTxxx","ns1:AdministrativeExpensesAMTxxx","ns1:EndYearBalanceAMTxxx","ns1:BoardDesignatedBalanceEOYPct","ns1:PrmnntEndowmentBalanceEOYPct","ns1:TermEndowmentBalanceEOYPct"]
["EIN","Taxpayer Name","CYBeginYearEndow","CYContributions","CYInvEarnLoss","CYGrantsScholarships","CYOtherExpenditures","CYAdminExpenses","CYEndYearEndow","PYBeginYearEndow","PYContributions","PYInvEarnLoss","PYGrantsScholarships","PYOtherExpenditures","PYAdminExpenses","PYEndYearEndow","2YBeginYearEndow","2YContributions","2YInvEarnLoss","2YGrantsScholarships","2YOtherExpenditures","2YAdminExpenses","2YEndYearEndow","3YBeginYearEndow","3YContributions","3YInvEarnLoss","3YGrantsScholarships","3YOtherExpenditures","3YAdminExpenses","3YEndYearEndow","4YBeginYearEndow","4YContributions","4YInvEarnLoss","4YGrantsScholarships","4YOtherExpenditures","4YAdminExpenses","4YEndYearEndow","CYEndYear%Quasi","CYEndYear%Perm","CYEndYear%Temp","5 YR % change","CAGR"]
[350869045,"DEPAUW UNIVERSITY",627746247,12890682,35472394,17311267,12164213,2847214,643786629,549015343,20703876,87003371,15299288,10731334,2945721,627746247,483049622,37975648,56506890,15469291,10931186,2116340,549015343,500923763,7262743,3842377,17113054,9286179,2580028,483049622,435056793,6593597,87755853,16306816,9530787,2644877,500923763,0.36840000000000001,0.50660000000000005,0.125,{"$f":"(I3-AE3)/AE3","v":0.47977606454704869},{"$f":"(I3/AE3)^(1/5)-1","v":8.1531566533079136E-2}]
[350868202,"WABASH COLLEGE",362448629,1439500,4823807,3598815,17428197,840299,346844625,339789723,3038561,40816593,3328416,16979239,888593,362448629,317968783,6774550,35218016,2840057,16486548,845021,339789723,331924878,5542318,269907,2740317,16075669,952334,317968783,287383239,4027977,57542864,2769877,13466711,792614,331924878,0.52700000000000002,0.47099999999999997,2E-3,{"$f":"(I4-AE4)/AE4","v":0.2069062420164316},{"$f":"(I4/AE4)^(1/5)-1","v":3.8328337448855887E-2}]

{"sheet":"Compensation","index":3,"n_rows":4,"n_cols":8,"rows_kept":4,"cut":false,"hidden":true,"merged":[]}
["Explore data -->",2,"ns1:TotalEmployeeCnt","ns1:CYSalariesCompEmpBnftPaidAmt","ns1:PYSalariesCompEmpBnftPaidAmt","ns1:TotalCompensationFilingOrgAmt"]
["EIN","Taxpayer Name","CYEmpCount","CYEmpTotalComp","PYEmpTotalComp","CYPresTotalComp","AvgEmpTotal Comp","Pres to Avg Ratio"]
[350869045,"DEPAUW UNIVERSITY",2150,55199442,54918794,673140,{"$f":"(D3-F3)/(C3-1)","v":25372.872033503954},{"$f":"F3/G3","v":26.529909547139287}]
[350868202,"WABASH COLLEGE",1259,26653494,25246154,543794,{"$f":"(D4-F4)/(C4-1)","v":20754.928457869635},{"$f":"F4/G4","v":26.200716668517831}]

{"sheet":"Tuition","index":4,"n_rows":4,"n_cols":8,"rows_kept":4,"cut":false,"hidden":true,"merged":[]}
["Explore data -->",3,"ns1:BusinessCd","ns1:TotalRevenueColumnAmt","ns1:CYGrantsAndSimilarPaidAmt",null,"ns1:TotalAmt"]
["EIN","Taxpayer Name","Description","Tuition Revenue","Student Grants","%Tuition Subsidized","Dom Student Grants","Int Student Grants"]
[350869045,"DEPAUW UNIVERSITY","TUITION AND FEES",91569987,49878256,{"$f":"E3/D3","v":0.54470091821679523},49435976,442280]
[350868202,"WABASH COLLEGE","TUITION & FEES",33507245,18691770,{"$f":"E4/D4","v":0.55784263970374171},638028,18033742]