How to Clean Your Data and Make it Look Slick

Every day we are inundated with articles, infographics and news reports that quote statistics that we are just supposed to accept at face value. Consider online real estate prices. If you search in your local area, you will find varying figures for median price, price per square foot and return on investment. The volume and variance in data leads to many questions: Who is supplying this information? What types of property does it include? What period does it cover?

As accountants, this unsubstantiated reporting should make us uncomfortable. We are a profession that prides ourselves on transparency and disclosure. When that isn’t forthcoming, our red flags should go up. How can we trust what we are reading when we know nothing about the source and quality of the underlying data? With all the advances in technology, accountants are uniquely positioned to be the champions who set higher standards for reporting. By giving the audience access to the data, we achieve the ultimate transparency. It’s not as hard or expensive as you may think.

I recently challenged myself to create a case study that analyzed real estate sales in my community—Panama City, Fla. Like many other resort areas, our beachfront county experienced wild fluctuations in property prices over the past decade. I was curious about property values, whether they were selling at a gain or loss and if the values used for tax purposes were fair.

I used several software programs to not only gather the data and crunch the numbers but also to provide an innovative way for others to interact with the records. The programs are fairly easy to run. There is a plethora of free web-based written and video tutorials from the software vendors and user groups to help you get started. The fastest way to learn is to jump right in and try it yourself. You can always go back to the tutorials for more in-depth instructions as needed. As the analyst, critical thinking is crucial. Computers are an incredible tool, but only a real person can gather, organize, apply and present the information in a meaningful way.

While my study focused on real estate, you can easily apply the same technology and methods to a wide variety of applications. The possibilities are endless and are not limited to financial data. I have seen examples analyzing the stock market, weather, baseball stats and Beatles songs. Any interesting subject with reliable and accessible data is fair game. This technique allows you to blend left brain logic with right brain artistry.

Here’s an overview of the steps:

GATHER THE DATA

I used WebHarvy, a web scraper/data mining tool, to rapidly copy and paste information from the public records online. It only costs $99 and works best with structured web data. I was impressed with their support. The program lets you choose what fields you want, extracts the data and saves it in an Excel file.

CLEAN, JOIN AND ANALYZE THE DATA

Cleansing the data is a highly important step that cannot be omitted. Outliers, blanks, and formatting errors can skew your results, so you need to address those before running any computations. Computer Assisted Auditing Techniques (CAAT) have revolutionized the process. I use ACL Analytics, which can be purchased on a subscription basis for $1,500 per year and features self-learning resources, as well as paid live training. Similar programs include IDEA and Arbutus. They allow you to classify, stratify and get statistics more easily and quickly than working with a spreadsheet program. They can also hold billions of rows of data whereas spreadsheet programs are typically limited to one million.

MAKE THE DATA VISUALIZATIONS

This is where everything comes to life; the “ta-dah” moment. Seeing the information graphically makes it so much easier to understand. Patterns and abnormalities you may have never noticed will stand out. Data visualization programs allow the audience to filter, drill down and download the data, as well as share graphics online. I used Tableau Public, a free data visualization program that offers free and paid self-learning options. There are many software choices in this arena, some free and others not. It depends if you want to share the information privately or make it available to the public.

PREPARE A WRITTEN PDF REPORT

I wanted a written report so people could print it for offline reading. However, I linked the graphs and tables to the data visualizations so anyone reading online can easily explore further. My choice for desktop publishing is Canva.com, a free online program that has very modern magazine style templates with hardly any learning curve.

As you can see, this process does not have to cost much money. With a little time and effort, you can actually take your reporting to the next level and your audience will get a much richer experience.

Christina Durta, CHFP, CPA, MST, Managing Member of Christina M. Durta, CPA, LLC. The majority of Christina’s career has been spent in internal auditing. Her first job was as an internal auditor for a Fortune 500 company. Her mentor there taught her the fundamental skills and creative mindset needed in that diverse field of accounting. She went on to work as an internal auditor for a hospital system. Today she has her own firm that focuses on using observation, testing and computer assisted audit techniques to help organizations recover or save money, make informed decisions or solve problems. She enjoys working with other accountants and auditors to support and enhance their services.

You can see the entire case study referenced in this blog post at http://nontraditionalcpa.com. There is a PDF report and over 20 interactive data visualizations.

House image courtesy of Shutterstock

How to Clean Your Data and Make it Look Slick

Related Stories