FINDING LOCAL VA DISABILITY CLAIM DATA
Monday Morning reports from the Veterans Benefits Administration track on a weekly basis the number of veterans who have submitted claims of injury or illness suffered while serving their country.
After two recent wars and the VA’s belated coverage of Agent Orange related health issues, the number of veterans filing disability claims has soared. In many cases, a delay in processing benefits causes a veteran to forgo treatment or pay for care out of pocket. As claims have literally piled up (see photos below), many reporters and advocacy groups cited most-recent figures obtained from these reports.
But when you open up the Excel spreadsheets from the VBA, you’ll find lots of figures with little explanation. So we’ve put together this guide to get you started in your pursuit of good local, state, regional and national stories that are to be had in the data. A grounding in Excel basics is needed to follow along.
About those tabs
The first spreadsheet tab (“Transformation”) details the national aggregate claims count broken into two categories: Compensation and pension. Generally, when we’re talking about veterans waiting for benefits, we’re talking about a compensation claim because it is a monthly sum paid to the vet to cover injuries suffered as a result of military service. Pension claims are a form of supplemental pay tied to the a veterans income.
When stories about the backlog count the number of veterans waiting for benefits they also are generally referring to the “Entitlement (Original and Supplemental)” field. Vets may have more than one claim filed with the VA but only their first one is tracked here.
Another important metric highlighted on this tab is “# Pending Over 125” and “% Over 125.” This is tracking claims taking longer than 125 days to process. That number has hovered well over the VA’s own internal target of 60 percent until recently (though it still hasn’t dipped below VA’s goal, but has come within striking distance) and is a key metric for gauging progress in reducing the backlog.
Many other types of compensation claims are counted in the Monday Morning Reports and most of them are straightforward. But one worth noting is “appealed claims.” These are filed by veterans who were turned away but appealed because they believe the VA made a mistake while handling their claim. Nationally, these veterans wait 3.5 years on average for a decision after having waited an average of 192 days to be denied. As of Nov. 30, 2013, there were 266,407 cases pending.
These values vary dramatically across the country and stories about the VA claims system are almost always more impactful when told at the local level.
The second tab — named “Rating Bundle Aggregate” — tracks progress at the 58 Regional Offices, or RO, where claims are processed, so you can compare against the national values and quickly spot outliers. This tab only tracks “rating claims,” which essentially require a VA employee to consider medical evidence and grade a veteran’s disability before he or she receives compensation. A “non-rating claim,” on the other hand, can be processed much more quickly because it doesn’t require a decision.
Most states have one central office to process claims but larger states such as California and Texas have multiple offices. You can look up offices for each state here.
This document (Word file) details each of the fields in the report, but we’ll highlight two in particular:
- Avg. Days Pending — The average number of days from the date a claim is received through the current reporting period for all currently pending claims. It is calculated by dividing the total number of days pending by the total number of pending claims.
- Completed Claims: Month to date — While the MMR is published weekly, this field tracks the number of claims completed per RO for the month up to the current week of the report. With a little algebra you can see how efficient the VA is by calculating the rate each RO is completing claims week to week.
There is one other sheet — named “Traditional Aggregate” — which tracks several other metrics such as pensions and appealed claims, as well as average days pending for non-rating claims. Before October 21, 2013, this was a primary source for data concerning the backlog. But the VA updated its Monday Morning Report in late October to include the “Rating Bundle Aggregate” tab. The Center for Investigative Reporting, a leader in analyzing and making available data from the VA reports, now tracks data from this tab because the VA has stated it contains data related to eliminating the backlog in 2015. (Editor’s note: Shifflett played a key role in developing CIR’s veterans data platform, including its APIs, when he worked there).
You can read a full report detailing the VA’s plan for tackling the backlog here (PDF) for a broader perspective of these numbers.
A few more notes about the Monday Morning Reports before moving on to the “Aspire” system. First, pension claims and the “education inventory” (which relates to GI Bill, for instance) are only handled at select offices. Second, the Office of Inspector General publishes reports about the VA’s performance. While data is great for context (The Center for Investigative reporting tracks these figures and more at each office over time) it’s the soldiers’ stories that prompt the public to care. The OIG reports often detail individual failures by the VA system and those stories can be traced back to and expanded with numbers in this report.
The VBA’s Aspire system “specifically depicts how each of our VA Regional Offices measure up to quality and other claim processing goals specific to our internal workload and processes.”
Aspire is broken into two sites: one to manage healthcare reports and the other to manage benefits (which relates to the backlog). The reports show one month’s data (benefits) or one quarter’s data (health care) by a region (Western states vs. Southern etc.) and regional offices.
You may want to skip the Aspire dashboard altogether. It can be clunky and difficult to navigate. Instead, you can download spreadsheets that contain all the data from the dashboard, with tabs going back a few years.
- If you’re after benefits information download this spreadsheet.
- If you’re looking for data related to health care use this spreadsheet
Generally, the VA healthcare system is considered to be one of the best and its data is mostly used by researchers. Advocacy groups and journalists generally pay attention to the benefits spreadsheet, so that will be our focus.
You have to do some manual parsing of this sizable spreadsheet to see any one field over time because each tab contains the data for a given month or quarter. So let’s start with an example of how to do that.
Say you’re working a story about how many claims the VA is making decisions on and how that has changed over time. We can do that one of two ways: compare the percentage change between two points in time or take the average over a period of time.
First, the Aspire Dashboard will tell you how many claims were completed in the most recent month nationally (129,488, or 20,512 claims shy of the goal for September). If you click on any of the fields it will take you to its definition (and make it difficult to return to your last screen). You’ll also only see one region’s worth of offices at a time. So right there were two good reason to abandon the dashboard and fire up the extracted spreadsheet.
Let’s demonstrate how to work with the data. This demonstration uses Excel for OSX; your version may look different. We used data as of August, 2013.
When you open the spreadsheet (which we’ll call the benefits spreadsheet), the first thing you’ll want to do is filter the data by the type of claim we’re looking for (“Compensation – Completed Claims.)” See below.
The notes column will indicate which offices are off target by stating “Within N% of current year’s goal,” but for now we want to see all offices over time.
- Create a new spreadsheet and copy / paste all the data from the “Location” column into it.
- Then for column B in the new spreadsheet create a column header indicating the date of the data: “2013_08.”
- Copy all the data from the “Value” column in the benefits spreadsheet into your “2013_08” column. It should look like the sheet pictured on the right.
Now, let’s repeat the process for two more dates.
- In the benefits spreadsheet flip to the sheet labeled “2013_07”, filter the data for “Compensation – Completed Claims” and copy the “Value” column into your new spreadsheet.
- Do the same for the “2013_06” sheet as well.
The slides below recap those steps. Click on the play icon or arrows in lower left to see each step. You can view full-screen as well.
Once you have three dates of data, we can figure out the percent change between August and June, as well as the three month average for each regional office.
We’ll use columns E and F in your new spreadsheet to calculate the change and average, respectively.
- Create your column headers: “PCT_CHANGE” and “AVERAGE.”
- Then in column E row 2, enter the percent change equation (new – old) / old. It will look like this for us: “=(B2-D2)/D2”.
- Then in column F row 2, use Excel’s average function for values across a range: “=AVERAGE(B2:D2).”
- Next, copy Column F and E, row 2, then highlight the cells below. Paste. Now you have the percent change and three-month average for completed claims for every regional office in the nation.
Another set of slides recap the steps:
Now you probably want to sort on the “PCT_CHANGE” column, smallest first, to find that the Providence Rhode Island RO saw the largest drop in completed claims. What causes the slowdown and is it important? That requires a bit more research. Call around to veterans groups and see what they have to say. You could create a FOIA to request the number of new claims filed in Providence. Maybe the drop precipitates an increase in the number of vets filing new claims without an increase in staffing to handle the additional workload.
If you’d like to see the final spreadsheet used in this example to experiment with, download it here from Google Drive.
It’s often the case with VA data that you’ll be given half of what you need and have to work with the VA to obtain the other half. Leads to important stories are almost always sitting on the surface.
While the Aspire and Monday Morning reports give definitions for each field, the meaning isn’t always clear. Double check with the VA about a field’s meaning if you’re not sure. It can be challenging to get ahold of someone who knows for sure but it’s worth taking the time to avoid building a story around a false pretense. While the VA makes a wealth of data available, it’s easy to misinterpret it, so seek out groups and experts that can validate your thesis and point you toward missing pieces.
Data from the Center for Investigative Reporting
The Center for Investigative Reporting parses the Monday Morning Report weekly and makes the data accessible via a convenient API.
There are a few additional fields you’ll find in CIR’s API that you won’t find in the MMR and were sourced from the VA during the newsgathering process. Those are included below. They cannot be updated weekly because the VA does not publish those figures on a regular basis.
- Veterans waiting on a disability claim = Pending Claims: The number of veterans waiting for a response from the VA for compensation for a disease, injury or illness linked to service in the military. Nationally, this number also includes about 10,000 survivors and other family members seeking compensation related to service-related injuries and diseases.
- Average days pending: detailed above.
- Average processing time: The average number of days a veteran waits for a decision from the VA.
- Average wait for new claims = Claims received average wait: The average number of days a veteran filing a claim for the first time waits for a response from the VA.
- Average time to decide an appeal: The average number of days a veteran waits for a response from the VA if they were denied their original claim and had to appeal.
- Completed claims: The number of claims processed by the VA by month.
- Claims received: The number of claims received by the VA by month.
- Claims completed per FTE: The number of claims processed per VA claims employee over the course of a year.
- Employees on duty: Number of claims staff working at the Veterans Service Center at each VA regional office.
- Claims pending >= one year: The number of unprocessed claims at least a year old, including appeals.
About the author
@shaneshifflett | LinkedIn | Personal site | GitHub