Financial Data Analysis Using Excel Power Query
Excel is widely recognized as the leading software tool for financial data analysis. Starting in 2010, Microsoft introduced powerful new features for Excel called Power Query and Power Pivot. The addition of Power Query extends the capabilities of Excel and introduces a new approach to transforming data for further analysis.
Both features are available as free add-ins to Excel 2010 and Excel 2013 via a simple download and installation. Beginning with Excel 2016 and business editions of Office 365, the features are included in the menu ribbon. They work in conjunction with a separate program, Power BI, and when used together, they form the basis for a business intelligence tool that is very cost-effective for small and medium-size businesses.
Massaging Raw Data
All business intelligence tools require that raw data be massaged so that it can be reported in an understandable and actionable manner. This is the area were Power Query shines, and it’s the means for analysis tasks performed by CFO Edge for our clients. Among the services we offer where we use Power Query are:
- Financial data analysis
- Financial planning
- KPI analysis
- Budgeting and forecasting analysis
- Project-based accounting analysis
- Time and entertainment expense analysis
- Inventory management analysis
These service offerings all have one thing in common: They require financial data analysis and interpretation. However, before this can be done, the data often must be modified or “scrubbed” so it can be useful in an analysis scenario. The process of transforming raw data into useful information is commonly described by the acronym ETLR, which stands for Extract, Transform, Load and Report. While many users of Excel are comfortable with scrubbing raw data using formulas, Power Query lets you more easily accomplish the first three steps in the ETLR process.
In an article I wrote that was published in the December 2017 issue of the Journal of Accountancy, a monthly publication of the American Institute of Certified Public Accountants (AICPA), I discussed the use of Excel to extract general ledger data from a QuickBooks file and Power Query to transform data so that it can be further analyzed in a pivot table. The article was written to describe the advantages of using Power Query compared to traditional formula-based approaches to transforming data by comparing its use to a financial data analysis of a company’s general ledger that was in an earlier article in the Journal of Accountancy.
You can read the article on the AICPA’s website by clicking here:
How to Boost Excel Efficiency with Power Query
Advantages of Power Query
Power Query offers many advantages over traditional formula-based ways of transforming data, but two stand out. The first is the presence of built-in utilities that replace formulas and reduce a multi-step process to a one-click action. For example, the article discusses how one click in Power Query replaces several steps that a formula approach needs to fill in missing data in a column.
The second advantage of Power Query is that saved queries can be re-used when the underlying data is updated with little or no additional work. This maintains the scrubbing and other modifications to the existing data and applies it to the new data.
Another advantage of Power Query is the ability to easily append new data to a file, or merge two or more files into a single file. These steps replace the more laborious cut-and-paste routine used in the former case and lookup formulas in the latter case.
Concluding Thoughts
Excel is widely recognized as the leading software tool for financial data analysis. Starting in 2010, Microsoft introduced powerful new features for Excel: Power Query and Power Pivot. The addition of Power Query extends the capabilities of Excel and introduces a new approach to transforming data for further analysis. Here at CFO Edge, we offer a wide range of services using Power Query. Please contact us to learn more about how we can use Power Query to help you improve financial and operational performance.
Arthur F. Rothberg, Managing Director, CFO Edge, LLC
No Comments