Excel VBA Form 990 Scorer
This project develops tools to semi-automate a specific style of philanthropic evaluation.
The Form 990 code uses Excel VBA to score IRS Form 990s submitted by 501(c)(3) organizations.
The scoring highlights entities that have endowments, award scholarships, and emphasize science education or research.
Getting Started
To get the system running:
1. Download `Code.xlsm` and place it in your working directory (e.g., `x/`).
2. Download the `.txt` files:
- `nodenames.txt`
- `stopwords.txt`
- `punctuation.txt`
- `rule.txt`
Place them in the same directory as the Excel file.
3. Download IRS Form 990 XML files from [IRS Form 990 Series Downloads](https://www.irs.gov/charities-non-profits/form-990-series-downloads).
4. Unzip those forms into `x/testforms/`, and create subdirectories:
- `x/testforms/990` for standard 990 files
- `x/testforms/errant` for nonstandard or filtered-out files
Input Files
The user may edit any of the supporting `.txt` files to change what data is parsed or scored.
### nodenames.txt
Each line defines:
- Data type (`String`, `Date`, `Integer`, `AbsInt`)
- Field length
- XML path to the node
Example:
```
Date;10;Return/ReturnHeader/TaxPeriodBeginDt
Integer;4;Return/ReturnHeader/TaxYr
AbsInt;15;Return/ReturnData/IRS990/CYInvestmentIncomeAmt
String;600;Return/ReturnData/IRS990/ActivityOrMissionDesc
```
### stopwords.txt and punctuation.txt
Used to clean and tokenize text fields—feel free to modify.
### rule.txt
Defines scoring logic for each rule. Users can modify or add rules.
Parsed & Scored Worksheets
- `Parsed990Data` contains extracted data:
- Headers: nodenames
- Rows: form unique IDs and their values
- `Scored990Data` contains rule evaluations:
- Headers: rule names
- Rows: binary scores (1 or 0)
## Rule Types
There are four rule types. Each uses a semicolon-delimited format:
### 1. `Substring`
```
Substring;RuleName;Nodename;Present;token1,token2,...
```
- Checks if tokens are present (or absent) in the specified text node.
### 2. `Trend`
```
Trend;RuleName;Nodename1,Nodename2,...
```
- Compares values across nodes for an upward/downward trend.
### 3. `Percentile`
```
Percentile;RuleName;Nodename;Cutoff
```
- Scores 1 if a value is above the given percentile cutoff.
### 4. `Eval`
```
Eval;RuleName;Nodename;NumOrTxt;Expression
```
- Evaluates logical expressions involving the node's value.
### Sample Rules from `rule.txt`:
```
Eval;Age;IRS990_FormationYr;Num;Year(Now()) - IRS990_FormationYr > 15
Substring;Web;IRS990_WebsiteAddressTxt;T;academy,edu
Percentile;EndYrBal;CYEndwmtFundGrp_EndYearBalanceAmt;0.50
Trend;YrNet;IRS990_NetAssetsOrFundBalancesBOYAmt,IRS990_NetAssetsOrFundBalancesEOYAmt
```