An ’Excel’-lent Introduction to CHI

“Can you work on getting the new CHAS data on the website?”

This request from Deb Goeken, our senior director of operations and communications, would be the beginning of my first substantial project as a research assistant at the Colorado Health Institute. Being new to the team, I didn’t know much about the Colorado Health Access Survey.

Nearly two months and 317 data tables later, I now know that this baptism by CHAS fire was the perfect introduction to working at the Colorado Health Institute.

The CHAS process was long underway when I started at the Colorado Health Institute in October. The survey, conducted every two years, assesses health insurance coverage, access to health care and use of health care in Colorado. Now in its third installment, the 2013 CHAS provides new insights into such areas as mental health, churn and long-term care insurance. The design, implementation and collection stages were complete. The final piece – how to post the data online – was still to be decided.

With such a large amount of data and a great deal of excitement over the ability to provide a baseline of Colorado’s health before the implementation of the Affordable Care Act, there were many important questions to consider. What would be the most helpful layout of the data? Which crosstabs should be included? How many is too many? Where would all of this information live on our website?

We found that the data could fall into one of ten categories. Each category would exist as its own Excel workbook and the workbooks would be available for download in the data section of our website. With this plan in place, the work of choosing variables, analyzing data and creating tables began.

Leading up to the launch, our Senior Data Analyst Rebecca Crepin and I worked in tandem to get the data ready for the web. While the work sometimes made our eyes cross, we knew how important it was to ensure that the end product was intuitive and easily transferrable to the user’s own presentations or reports. After two solid weeks of “Excel-ing” and many to-do lists later, our result was 203 tables displaying data from all three surveys, posted in time for the CHAS 2013 launch on Tuesday, November 5.

Following the CHAS launch week, we revisited the data to take into consideration the feedback from event attendees. There was great interest in looking at the data by demographics, so we created a new demographics workbook that includes 114 new tables crossing data by age, race/ethnicity, gender, employment, education and income. Some 2013 data highlights from the new demographics workbook:

  • Men (30.8 percent) are more likely than women (16.7 percent) to cite “don’t need insurance” as a reason for being uninsured.
  • Four of ten (41.9 percent) Coloradans without a high school diploma or equivalent report being uninsured. The rate of uninsurance drops to 22.5 percent among high school graduates or those with an equivalent degree.
  • More than one of five (21.8 percent) Hispanics report being uninsured compared to 11.7 percent of non-Hispanic whites.
  • One of four non-Hispanic blacks (25.9 percent) report having no usual source of care.  For non-Hispanic whites, the rate of having no usual source of care is 14.6 percent.
  • Only 16.7 percent of non-Hispanic blacks report excellent health status compared to 34.9 percent of non-Hispanic whites.
  • 21.6 percent of the unemployed report that there was a time they needed mental health care or counseling services but did not get them. Only 7.5 percent of the employed responded that they did not get needed care.

In the coming months, CHI will take a closer look at the data from all 11 workbooks to analyze changes that may have occurred since the 2009 and 2011 surveys and to forecast how some results may change in the 2015 survey after the implementation of the Affordable Care Act.

As for me, I look forward to building on the tables I’ve already created and to writing about the new data in the Insights section of our website. The website data project gave me the chance to work closely with a number of my new colleagues including Deb, Rebecca, research analyst Natalie Triedman and senior analyst Tasia Sinn, familiarized me with the wide range of indicators measured by the CHAS and introduced me to working on our website – not to mention all of the new Excel tricks I’ve picked up along the way.