SQL for Financial Reporting

SQL should be part of every modern financial reporting process. It can deal with gigabytes of data and perform financial calculations in a split second. It’s time to transition from Abacus (also known as Excel!) and start using SQL in financial reporting.   

Data has taken a central part in business decision making. This applies even more to financial data. The main goal of businesses is to make a profit, so it’s no wonder that financial data plays a crucial role in the strategic decision-making process of any modern business. Such companies like to call themselves data-driven.

In a data-driven company, decisions are backed by historical and current financial data. Data is crucial to understand whether the company’s performance is improving and how it compares to your competitors and your industry average. You can even use advanced statistical techniques on historical data to predict future trends.

SQL is an indispensable tool for businesses. In financial reporting, we’re talking about huge amounts of data.  You can’t handle it manually. SQL is a programming language designed for querying databases and manipulating data, which makes it ideal for financial data management.

You can see how SQL for financial reporting works in our SQL Basics course. You’ll learn to retrieve data from one or multiple tables, filter and aggregate data, write complex instructions, use set operators, and perform calculations. In addition, our SQL Reporting track teaches you SQL in financial reporting scenarios. Two significant parts of this track explore revenue trends and customer behavior analyses using SQL. In our courses, learning is interactive; we feature hundreds of real-life examples where you can learn and practice SQL reporting techniques.

Can SQL Handle Financial Data?

I just asked SQL this question and it said: “Sure, no problem!” So, there you go; to SQL, it really doesn’t matter what type of data you’re talking about. Financial data is just data to SQL. The only requirement is that data is saved in databases.

Are you interested in analyzing the daily prices of thousands of shares? Or daily changes in FX rates? Maybe products sold, costs incurred, tracking inventory? SQL handles all this very easily. Let me show you several examples of using SQL for financial reporting. 

Simple Example: Finding All the Sales From March 2024

Suppose you have the table sales. It contains sales data from 1 January 2024 to 30 April 2024. Here’s a snapshot of it:

In Excel, you probably do it like this: select the date range in the filter, copy the results to another sheet, and there you have all the sales for March 2024. But SQL can filter data, too, and it does it much quicker and easier. Here’s how we use WHERE to find all the sales for March 2024:

SELECT id, 
	 date,
	 value
FROM sales
WHERE date BETWEEN '2024-03-01' AND '2024-03-31';

In SELECT you, well, select all the columns that you need in the output. In FROM, you put the name of the table you’re querying. This is the basic skeleton query you’ll use in most cases; the rest is customized to your needs.

In WHERE, you write the name of the column you want to filter. Then, you define the start and end dates using the operators BETWEEN and AND.

In the end, you get sales for every day in March 2024. To save space, I’ll show only the first ten rows.

Another Simple Example: Summing All the March 2024 Sales

Now that you have separated the March 2024 figures from the rest, you can sum them to get the total March sales. For this, SQL has the SUM() function. In an SQL query, you can filter and sum data all at once:

SELECT SUM(value) AS total_march_2024_sales
FROM sales
WHERE date BETWEEN '2024-03-01' AND '2024-03-31';

Aside from the SUM() function, the query stays basically the same. It filters the same dates and then sums all the filtered values.

One short query and you get this result:

Less Simple Example: Month-Over-Month Sales Comparison

This is something you very often do in financial analysis: calculate the increase/decrease compared to the previous period. It’s one of the very common SQL queries for finance.

This query is quite a bit longer than the others, but there’s no need to understand every part of the code. I just want to show you how suitable SQL is for financial reporting. Have a look:

WITH monthly_sales AS (
  SELECT TO_CHAR (date, 'YYYY-MM') AS month,
         SUM(value) AS total_sales
  FROM sales
  GROUP BY month
  ORDER BY month
)
  
SELECT month,
       total_sales,
	 total_sales - LAG(total_sales) OVER (ORDER BY month) AS difference_previous_month
FROM monthly_sales;

Essentially, this is a two-part query. The first part (starting with WITH and ending with ORDER BY month) turns dates into months of the year and aggregates the sales value, so we get this output.

See, we get all the values calculated already and labeled neatly. If we just ran this part of the query, this is what we’d get. But we also want to compare these values.

The second part of the query takes each month’s sales and compares them to the previous month. This is done using the LAG() function. This is what’s known as an SQL window function, or a function that works on a window (i.e. a subset of the data). Window functions are very useful in SQL financial analysis.

When you run the complete query, you get this output. Lovely, isn’t it?

The data is neatly labeled, sales are summed for each month, and there’s a month-over-month comparison. Imagine how much time and work this would require in Excel!

Let’s quickly analyze the output. January has a NULL because it’s the first month, so you can’t compare its sales to anything. February and March show an increase in sales, while April shows a significant drop compared to the previous month. This information could be telling you something; you should probably find the reason for this drop. Is it seasonality? A new competitor entering the market? That’s up to you to answer.

For more complex examples of SQL for financial reporting, look at these three real-life examples. Also, here’s an article dedicated to calculating revenue growth in SQL.

The Trouble with Traditional Financial Reporting Tools

Today, financial reporting still heavily relies on using Excel or similar tools. You know what I mean: the workbooks with an ever-increasing number of worksheets. Data is inserted or deleted in every worksheet. The number of filters, VLOOKUPs, links to previous sheets, formulas like SUM(), AVG(), IF(), IIF(), conditional formatting, and pivots used is endless.

This is a lot of manual work. Guess what! Manual work is time-consuming and error-prone.

When I worked in financial reporting many moons ago, I also mainly used Excel. Whenever I was asked if my reports were correct, I would reply: “I’m only sure it’s reasonably correct.” (Surprisingly, I didn’t get fired for saying that.)

The thing is, everyone who creates reports with so much manual work knows that mistakes are waiting to happen.

Everything is wonderful when the reports are reasonably correct. What if they’re unreasonably incorrect and nobody notices it? You only realize the mistake next quarter: you compare the figures with the previous quarter, and they simply don’t compute. Instant cold sweats! You open your Excel and realize the freaking VLOOKUP() picked up wrong values because someone (was it you?) messed with the columns. Is it too much to think I’m not the only one who experienced that? Or was I just terrible at my job?

There’s a remedy for all those near-heart-attack experiences.

SQL for Modern Financial Reporting

Yes, the remedy is using SQL for financial reporting. Writing SQL queries replaces all those bulging Excel sheets.

Use SQL in financial reporting and you can easily add and remove data, merge it, filter it, and aggregate it. You can do some sophisticated stuff, such as time-series analysis, calculating running totals, moving averages, or grouping data on multiple levels. And you can do it quicker and more accurately than in Excel. Here are some examples of using SQL to analyze financial data.

In other words, SQL replaces most of your manual work. This will greatly reduce the number of points where errors can happen. Your data and reports will be more accurate. This time, you can even say that the report is 100% correct and stand behind it.

You’ll produce analyses and reports several times faster, and you can make your financial reporting dynamic. Once you set up your SQL queries, you can save them and re-use them whenever you need to create a report. I hear you say, “But the dates change for every report!” No problem; make a few little tweaks in the query dates, re-run the query, and out comes your report – no need to go through all the steps over and over again.

Using SQL for financial data management gives you more independence and control. No more asking your more tech-savvy colleagues to extract data from the database for you. No more waiting for them to do that. No more waiting (again!) for them to do the same thing (again!) simply because they gave you the wrong data. Did they misunderstand you? Or they simply made a mistake? Not your worry anymore!

With SQL, you get whatever data you want and when you want it.

In addition, your relationships with your colleagues will improve, as they won’t have to bother with that pesky person from Finance anymore.

A Door to More Advanced Financial Reporting

Leveraging SQL in financial reporting ups your game. It opens the door to more advanced financial analysis and reporting techniques.

For example, SQL can integrate with other technologies and software, such as your beloved Excel. It can be found in SQL business intelligence tools – Tableau, Power BI, and Qlik Sense come to mind – that enhance the possibilities of creating comprehensive financial reports, visualizations, and dashboards.

With SQL, you can query the on-site databases and transition to using Cloud technologies. Cue Amazon Web Services (AWS), Microsoft Azure, or Google Cloud.

And the best part is that SQL can be used in financial modeling and forecasting. All the forecasting is based on historical data. You can use SQL queries in finance to analyze historical financial data, then apply advanced techniques and predict financial trends in:

• Revenue
• Expenses
• Cash flows
• Sales
• Inventory needs
• Customer churn
• Gross margin
• Working capital
• Profit
• Share prices
• FX rates
• Interest rates
• Dividends
• Debt repayment
• Future investments
• Break-even points

What Do I Do Now?

The only answer is that you start learning SQL for financial reporting. Our SQL Basics course and SQL Reporting learning track are tailored to financial professionals. In these courses, you’ll find real-world financial scenarios and interactive exercises. Not only will you learn SQL syntax, but you’ll also discover how to apply these skills in financial reporting.

Learn SQL for Finance

I know it’s sometimes hard to break the habits – the old We’ve always done it this way maxim.

However, ignoring new technologies that can make your work better, quicker, less error-prone, and more advanced is not wise. Mind you, SQL is not new at all; it’s been around since 1974 (50 years!) and has been widely used since the 1980s. So, it’s about time to ditch Excel as your primary tool and start using SQL.

Trust me, I know; Excel also used to be everything for me. SQL seemed like sorcery; write something on a blank screen, press play, and data comes out. Dark forces must be behind that! Once I overcame my initial fear of SQL queries, I realized how practical SQL is for financial reporting. It’s almost like it was made for working with data. Probably because it was.

And I was not the only one who made this transition. So, I encourage you to start with our SQL Basics course and then move on to our SQL Reporting learning track. If you need an idea of what to do next, here are some suggestions on how to approach learning SQL and some of the best online SQL courses for financial experts.

Give it a try, and you’ll notice the change! You’ll enhance your reporting processes. More importantly, you’ll be able to contribute more significantly to strategic decision-making in your company and even boost its profitability.