Data Analysis – Selecting the Right Tools

As promised, this blog is a continuation of last week’s topic, data analysis. We will dive into the third tip related to selecting the right tools. Tools have been used for ages to help humans perform complex tasks. From the creation of the wheel that accelerated transportation to the invention of electricity shaping the way we live, tools help us accomplish tasks that otherwise might appear impossible.

When I was just a wee lad, I used tools to take things apart and put them together. I always looked forward to helping my dad with weekend projects. I wanted to learn as much as possible. The learning started with fetching tools and working my way up to using them.

He would say, “Hey son, get me the needle nose pliers.” I would dig around for a while in his big toolbox. Then I would ask, “Which ones are those?” “The ones with the yellow handles,” he’d respond. I would gather all the pliers with yellow handles and bring them to him so he could pick the pair he needed. Then I knew which ones were needle nose. Sometimes he would test my knowledge by asking me to get the left-handed screwdriver. Just think on that one for a while. I learned a lot about tools, cars, and life. I had so much fun, and I got to spend time with my dad, which was the best part.

Carpenters, painters, and mechanics all use tools to perform their jobs. Each tool has a specific purpose, and the person who selects the right tool and knows how to use it will complete the job in an efficient and effective manner.

Back to financial data analysis, we have a variety of tools available to us, but we may not be comfortable with all of them. This is a problem because too many of us are comfortable with one tool and try to use it for every project. This can create inefficiencies and prevent us from running certain analytics because the software is limiting. We need to be familiar with each tool’s functionality and start learning how to use all of them. The following are brief overviews of tools that are easy to get your hands on:

• Microsoft Excel – Excel is spreadsheet software that is user friendly and has a lot of data analytic tests that can be executed quickly.
  • Pros – I usually use this software at the beginning and the end of most projects. At the start, I will run a few quick tests to understand the data by sorting or filtering or running a pivot table. With regard to ending a project, Excel is a good tool to create charts and tables, which allows you to make your findings visually appealing.
  • Cons – This ease of use comes with a price because Excel cannot easily compare or merge two data tables. I have seen people try to use the v-lookup or h-lookup functions for this task, but that isn’t how you are supposed to use these functions and can lead to a high error rate with a ton of wasted time. Another drawback for Excel is the processing speed when analyzing large amounts of data. Once you get into tens of thousands of records, you should consider stepping up to a data analysis tool like IDEA.
• Microsoft Access – Access is database software that is still fairly user friendly, but it requires some training. You will also have to use it on a somewhat regular basis to keep your skills sharp. It can do a lot of things beyond data analysis, but in this blog, I will focus on the data analysis functionality.
  • Pros – Access can be useful when formatting your data. For example, if you obtain data either in Excel or if you open a data file using Excel and some of the data is recognized as text rather than as a date (or an amount, another common data type), this can be fixed very quickly using Access. You can import the data, and through the import process, set up each field as the appropriate data type. This will allow you to begin using the data in Access or Excel. Another benefit is the fact that you can save certain operations and repeat them later with a new data set. Finally, Access picks up where Excel drops off with comparing or merging two data sets based on a common field. It is seamless and error free, but the records must match exactly, which leads me to a weakness.
  • Cons – With Access, it takes longer than Excel to perform certain tasks, and it takes a bit more training. As an example, writing expressions (i.e., formulas) can be more challenging. It takes a little more time to query (i.e., filter) data. It will also take some time to get accustomed to not viewing all the data in the same place. The data remains in the original tables and queries pull the data together for you to view it. There are also challenges related to reporting and summarizing the data in a visually appealing way without writing separate reports, which are really not worth the time investment if you aren’t going to use them for a future project. Additionally, you may have realized that some of the vernacular is slightly different.
• CaseWare’s IDEA – IDEA is software built for data analytics. There are multiple features built into the software that are crossovers from Excel and Access. There are several other software programs on the market (e.g., ACL) that are similar to IDEA.
  • Pros – IDEA has the ability to perform all the pros of Access in a slightly more user-friendly way. Also, there are functions that allow you to compare one record in a data set to another record in the same data set. As an example, you can perform a gap or duplicate analysis of check number, or you can run an aging or Benford’s Law analysis. These can be done fairly quickly in a user-friendly way. This cannot be performed easily in Excel or Access. Another very cool function of IDEA is the fact that you can add to the columns in a data set, but you can’t change the original data once uploaded. There is also a log that tracks the data analytics as they are performed, and you can also repeat previously performed analytics on the same data set or new data sets.
  • Cons – Again, more training time and practice are required to effectively use IDEA, but the investment is well worth it. The reporting and summarization features are not as effective as Excel. Depending on the amount of data, the software can become bogged down when running complex analytics or comparing/merging data from different data sets.
• Other tools – There are many other tools available for data analytic purposes. There are visualization software products for projects that may require mapping or categorizing data based on location or specific criteria. There are larger database software programs (e.g., SQL) that may be necessary depending on the quantity of records. There are more and more tools available on the market that you must evaluate to determine what will be best for your projects.

Now that you know a little bit more about these tools, I hope that you will try to use them on your next data analysis project. For the experts out there, please feel free to comment with additional insights and feedback. I felt challenged with this topic to include all the pros and cons. I also know that some of the cons mentioned above can be done, but the point is that there may be a better tool to use for performing a certain function.

Next week’s blog will be about planning and performing data analysis to provide some tips. 

For weekly insights on fighting financial fraud, click here: