Managing Data Integrity for Finance: Discover practical data quality management strategies for finance analysts and data professionals

Managing Data Integrity for Finance

Copyright © 2024 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

Group Product Manager: Kaustubh Manglurkar

Publishing Product Manager: Arindam Majumdar

Book Project Manager: Farheen Fathima

Senior Editor: Rohit Singh

Technical Editor: Rahul Limbachiya

Copy Editor: Safis Editing

Proofreader: Safis Editing

Indexer: Manju Arasan

Production Designer: Joshua Misquitta

DevRel Marketing Executive: Nivedita Singh

First published: January 2024

Production reference: 1160124

Published by Packt Publishing Ltd.

Grosvenor House

11 St Paul’s Square

Birmingham

B3 1RB, UK.

ISBN 978-1-83763-014-1

www.packtpub.com

Contributors

About the author

Jane Sarah Lat is a finance professional with over 14 years of experience in financial management and analysis for multiple blue-chip multinational organizations. In addition to being a Certified Management Accountant (CMA U.S.) and having a Graduate Diploma in Chartered Accounting (GradDipCA), she also holds various technical certifications, including Microsoft Certified Data Analyst Associate and Advanced Proficiency in KNIME Analytics Platform. Over the past few years, she has been sharing her experience and expertise at international conferences to discuss practical strategies on finance, data analysis, and management accounting. She is also the President of the Institute of Management Accountants (IMA) Australia and New Zealand chapter.

About the reviewers

Joshua Arvin Lat is the Chief Technology Officer (CTO) of NuWorks Interactive Labs, Inc. He is also a globally recognized AWS Machine Learning Hero. He previously served as the CTO of three Australian-owned companies and also served as the Director for Software Development and Engineering for multiple e-commerce start-ups. He is the author of the books Machine Learning with Amazon SageMaker Cookbook, Machine Learning Engineering on AWS, and Building and Automating Penetration Testing Labs in the Cloud. Due to his proven track record in leading digital transformation within organizations, he has been recognized as one of the prestigious Orange Boomerang: Digital Leader of the Year 2023 award winners.

Nathania Wijanto is a senior financial analyst with over seven years of experience in financial management and data analytics. She currently works at a large financial services firm in Sydney, combining technical expertise in data analysis and financial acumen to drive actionable insights. Prior to that, she worked at a Big Four firm and an American telecommunications company to streamline reporting processes and improve data quality, as well as drive valuable insights to support financial and operational decisions.

William Bowrey is an experienced Finance Leader with over 30 years of experience working for multinational corporations in financial planning, analysis, reporting, and accounting roles. He currently works for a large customer experience BPO and technology company where business insight has been driven through the implementation of key integrated management reporting systems that marry financial data with operations, sales, and human capital data, producing reliable, actionable, and timely business analysis. Prior to that, he worked in manufacturing and sales support roles, delivering financial analysis for turnkey projects.

Table of Contents

Preface

Part 1: Foundational Concepts for Data Quality and Data Integrity for Finance

1

Recognizing the Importance of Data Integrity in Finance

Understanding the impact of data integrity issues in finance

Lack of trust in systems

Damage to reputation

Financial impact

Compliance issues with laws and regulations

A quick tour of concepts relevant to data integrity management

Levenshtein distance

Machine learning

Orphaned records

Financial reporting

Balance sheet

Profit and loss statement

Cash flow statement

Budgeting

Forecasting

Depreciation

Variable cost

Risk management

Insurance

Transaction

Mutual exclusion

Debunking the myths and misconceptions surrounding finance data integrity management

Myth 1 – only large financial organizations are concerned about data integrity

Myth 2 – only finance professionals should be concerned about data integrity

Myth 3 – only internal financial reporting systems are affected by data integrity issues

Myth 4 – processes that improve data integrity are expensive and difficult to implement

Myth 5 – only electronic data is affected by data integrity issues

Summary

Further reading

2

Avoiding Common Data Integrity Issues and Challenges in Finance Teams

Detecting manual data encoding issues in finance teams

Utilizing available tools to check for data integrity issues in encoded data

Regularly audit encoded data

Monitoring and recording changes

Having the right team structure and composition

Putting robust data governance and compliance policies and procedures in place

Avoiding common reconciliation errors and mistakes in finance teams

Understanding common reconciliation errors

Preventing reconciliation errors

Preventing balance sheet data integrity issues

Implementing strong internal controls

Utilizing trustworthy data sources

Well-documented policies and procedures

Employing technology and automation

Handling data corruption and financial transaction data integrity issues in internal systems and databases

Risk assessment of possible data corruption

Establishing detection systems

Implementing preventative measures

Performing regular security audits

Summary

Further reading

3

Measuring the Impact of Data Integrity Issues

Technical requirements

Why measure the impact of data integrity issues?

To manage the risk of basing decisions on bad data

To manage the risk of not complying with regulations

To manage the risk of damage to reputation

Reviewing the relevant data quality metrics for financial data and transactions

Accuracy

Completeness

Consistency

Timeliness

Validity

Data profiling using a data quality framework

Define the criteria for data quality

Gather and evaluate the data

Analyze the quality of your data

Identify and prioritize data quality issues

Create a plan for remediation

Track and gauge the data quality

Preparing a sample data quality scorecard in Microsoft Excel

Establish the data quality metrics to be used

Define the scale for scoring KPIs

Assign a weight for the KPI

Get the overall score for the KPI

Create the template in Excel

Scoring the KPIs

Update the scorecard regularly

Preparing a sample data quality scorecard in Google Sheets

Establish the data quality metrics to be used

Define the scale for scoring the KPIs

Assign a weight for the KPI

Get the overall score for the KPI

Create the template in Google Sheets

Scoring the KPIs

Microsoft Excel and Google Sheets functionalities to improve data quality and integrity

Version control

Collaboration tools

Data validation

Conditional formatting

Summary

Further reading

Part 2: Pragmatic Solutions to Manage Financial Data Quality and Data Integrity

4

Understanding the Data Integrity Management Capabilities of Business Intelligence Tools

Technical requirements

Recognizing the importance of BI tools

Exploring common data quality management capabilities of BI tools

Data profiling

Data cleansing

Data validation

Data lineage

Data governance

Reviewing the most popular BI tools and how to get started with them

Microsoft Power BI

Tableau by Salesforce

Alteryx analytics cloud platform

Summary

Further reading

5

Using Business Intelligence Tools to Fix Data Integrity Issues

Technical requirements

Managing data integrity issues with BI tools

Ensuring consistent data type formatting

Data profiling features

Column quality

Column distribution

Column profile

Data cleansing methods

Removing empty cells

Removing duplicates

Identifying data outliers

Managing relationships in data models

Dealing with large financial datasets using data validation

Summary

Further reading

6

Implementing Best Practices When Using Business Intelligence Tools

Technical requirements

Handling confusing date convention formats

Using data visualization to identify data outliers

Visualizing using a scatter chart

Visualizing using a histogram

Managing orphaned records

Identifying orphaned records in Power BI

Identifying orphaned records in Alteryx

Summary

Further reading

7

Detecting Fraudulent Transactions Affecting Financial Report Integrity

Technical requirements

Understanding the major causes of fraud

Common myths and misconceptions about financial fraud

Myth 1—the impact of fraud is insignificant

Myth 2—fraud is very hard to detect

Myth 3—prosecution completely deters fraud

Myth 4—preventing fraud is only important for big institutions

Myth 5—large companies are the common targets of fraud

Interpreting financial reports

Horizontal or trend analysis

Vertical analysis

Competitor and industry analysis

Cash flow analysis

Learning how fraudulent transactions affect overall financial report integrity

Fictitious revenues

Improper capitalization of expenses

Misrepresentation of liabilities and debt

Detecting and preventing fraudulent transactions and anomalies

Tone at the top

Implementing strong internal controls

Management review

Ratio analysis

Utilizing data analytics and machine learning in fraud detection

Summary

Further reading

Part 3: Modern Strategies to Manage the Data Integrity of Finance Systems

8

Using Database Locking Techniques for Financial Transaction Integrity

Technical requirements

Getting started with SQL

Installing PostgreSQL

Creating a database

Creating a table

Inserting data into the table

Learning how race conditions impact the transaction integrity of financial systems

Reviewing how database locks prevent financial transaction integrity issues

Guaranteeing transaction integrity with database locks

Best practices when using database locks

Summary

Further reading

9

Using Managed Ledger Databases for Finance Data Integrity

Technical requirements

Introduction to ledger databases

Creating an AWS account

Creating an S3 bucket

Creating the Amazon QLDB ledger

Reviewing the internals of ledger databases

Getting the digest

Creating a table

Using the PartiQL editor

Generating a document

Saving and retrieving a query

Viewing the data in the table

Loading saved queries

Nesting automatically

Understanding how ledger databases prevent data integrity issues

Verifying the document

Updating the transaction

Obtaining the digest

Verifying the results

Deleting records from the ledger

Working with history and data

Exporting the journal

Cleaning up

Exploring the best practices when using ledger databases

Summary

Further reading

10

Using Artificial Intelligence for Finance Data Quality Management

Technical requirements

Introduction to AI

Applications of AI in finance

Detecting anomalies in financial transaction data

Handling missing financial reporting data with AI

Best practices when using AI for data integrity management

Summary

Further reading

Index

Other Books You May Enjoy

Preface

Maintaining the integrity and reliability of financial data is key to the success of any organization as more companies around the world have been using financial and operational data to make business decisions. If you’ve been working in the industry for a long time, you probably know by now that data integrity management plays a critical role in helping ensure compliance and avoiding significant financial penalties as well. Unfortunately, there is a big gap when it comes to the proper analysis and management of financial data in organizations globally. In addition to this, companies building their own internal applications and systems are not equipped with the knowledge and experience to guarantee the integrity of the financial data in the databases used to store transactions and generate reports.

I’ve written this hands-on book to help finance, data, and technical professionals learn various concepts and practical solutions to manage the integrity of the financial data used by various types of organizations. This will be equally useful to those planning to build their own internal systems and processes for handling financial transactions, records, and reports. Whether you are a beginner or a seasoned professional, this book is for you!

Who this book is for

This book is intended for financial analysts, technical leaders, and data analysts interested in learning practical strategies for managing data integrity and data quality using relevant solutions, tools, and strategies.

What this book covers

Chapter 1

, Recognizing the Importance of Data Integrity in Finance, gives a quick overview of the concepts relevant to the succeeding chapters in the book.

Chapter 2

, Avoiding Common Data Integrity Issues and Challenges in Finance Teams, dives deep into the data integrity issues and challenges faced by different finance teams.

Chapter 3

, Measuring the Impact of Data Integrity Issues, teaches you how to develop and generate data quality scorecards using a framework.

Chapter 4

, Understanding the Data Integrity Management Capabilities of Business Intelligence Tools, focuses on the common data quality capabilities of business intelligence tools and more popular tools online.

Chapter 5

, Using Business Intelligence Tools to Fix Data Integrity Issues, teaches you how to use business intelligence tools in order to solve data integrity issues.

Chapter 6

, Implementing Best Practices When Using Business Intelligence Tools, guides you on how to implement various best practices when using business intelligence tools.

Chapter 7

, Detecting Fraudulent Transactions Affecting Financial Report Integrity, focuses on processes and strategies to detect fraudulent transactions that affect financial report integrity.

Chapter 8

, Using Database Locking Techniques for Financial Transaction Integrity, dives deep into how specific SQL and database techniques prevent transaction data integrity issues.

Chapter 9

, Using Managed Ledger Databases for Finance Data Integrity, teaches you how to use managed ledger databases to enforce data integrity in financial systems and applications.

Chapter 10

, Using Artificial Intelligence for Finance Data Quality Management, exposes you to artificial intelligence solutions relevant to data quality and data integrity management.

To get the most out of this book

You are expected to have a basic understanding of concepts relating to finance, accounting, and data analysis. Basic knowledge of finance management is not required but will help with grasping the intermediate topics of the book.

If you are using the digital version of this book, we advise you to type the code yourself or access the code from the book’s GitHub repository (a link is available in the next section). Doing so will help you avoid any potential errors related to the copying and pasting of code.

Download the example code files

You can download the example code files for this book from GitHub at https://github.com/PacktPublishing/Managing-Data-Integrity-for-Finance

. If there’s an update to the code, it will be updated in the GitHub repository.

We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/

. Check them out!

Accessing high-resolution images

The high-resolution version of images used in this book are accessible on GitHub at https://github.com/PacktPublishing/Managing-Data-Integrity-for-Finance/tree/main/images

.

Conventions used

There are a number of text conventions used throughout this book.

Code in text: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: Using the SELECT FOR UPDATE statement applies a row-level lock on that row and waits for the previous instance to complete before going to the next.

A block of code is set as follows:

INSERT INTO Accounts (AccountID, Balance)

VALUES (1, 100.00);

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

CREATE TABLE Accounts (

    AccountID int,

    CustomerName varchar(100),

Balance decimal(10, 2) CHECK (Balance >= 0)

);

Bold: Indicates a new term, an important word, or words that you see onscreen. For instance, words in menus or dialog boxes appear in bold. Here is an example: To access the Data Analysis GPT, click on Explore in the sidebar and select Data Analysis from the list of GPTs available.

Tips or important notes

Appear like this.

Get in touch

Feedback from our readers is always welcome.

General feedback: If you have questions about any aspect of this book, email us at [email protected]

and mention the book title in the subject of your message.

Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/support/errata

and fill in the form.

Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected]

with a link to the material.

If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.

Share your thoughts

Once you’ve read Managing Data Integrity for Finance, we’d love to hear your thoughts! Please click here to go straight to the Amazon review page

for this book and share your feedback.

Your review is important to us and the tech community and will help us make sure we’re delivering excellent quality content.

Download a free PDF copy of this book

Thanks for purchasing this book!

Do you like to read on the go but are unable to carry your print books everywhere?

Is your eBook purchase not compatible with the device of your choice?

Don’t worry, now with every Packt book you get a DRM-free PDF version of that book at no cost.

Read anywhere, any place, on any device. Search, copy, and paste code from your favorite technical books directly into your application.

The perks don’t stop there, you can get exclusive access to discounts, newsletters, and great free content in your inbox daily

Follow these simple steps to get the benefits:

Scan the QR code or visit the link below

https://packt.link/free-ebook/9781837630141

2. Submit your proof of purchase

3. That’s it! We’ll send your free PDF and other benefits to your email directly

Part 1: Foundational Concepts for Data Quality and Data Integrity for Finance

This part covers important concepts relating to data quality and data integrity relevant to finance, data, and tech professionals.

This part has the following chapters:

Chapter 1

, Recognizing the Importance of Data Integrity in Finance

Chapter 2

, Avoiding Common Data Integrity Issues and Challenges in Finance Teams

Chapter 3

, Measuring the Impact of Data Integrity Issues

1

Recognizing the Importance of Data Integrity in Finance

Imagine if everyone online suddenly started complaining on social media that their bank savings accounts had unauthorized deductions. This is exactly what happened when thousands of customers of one of the major banks in Southeast Asia discovered that their account balances ended up negative due to duplicate transactions! This led to customers feeling anxious while reports of this data integrity issue went viral online. How would you feel if your hard-earned money suddenly disappeared overnight due to a data integrity issue?

Maintaining the integrity, accuracy, and reliability of financial data is key to the success of any organization. Data integrity plays a crucial role in finance, as business owners and decision-makers utilize financial and operational data in making long-term business decisions. If you’ve been working as a finance professional for a long time, you probably know by now that data integrity management plays a significant role in helping ensure compliance and avoiding significant financial penalties. Understanding the relevant concepts and strategies is the first step for every professional trying to master the art of financial data integrity management. In this introductory chapter, we will examine the importance of data integrity in finance and demystify various key concepts relevant to the succeeding chapters of this book.

That said, we will cover the following:

Understanding the impact of data integrity issues in finance

A quick tour of concepts relevant to data integrity management

Debunking the myths and misconceptions surrounding finance data integrity management

With these in mind, let’s get started!

Understanding the impact of data integrity issues in finance

Can you spot the wolf hiding among the sheep in Figure 1.1? In finance, the presence of data integrity issues can be compared to a wolf hiding among a flock of sheep. Much like the wolf presents a hidden threat to the sheep, a single data integrity issue can negatively impact the entire financial system’s reputation and stability.

Figure 1.1 – A wolf hidden among sheep

The wolf symbolizes the subtle yet potentially catastrophic effects of a data integrity breach. While data integrity issues such as corrupted financial records, inaccurate reporting, and duplicated transactions due to software bugs might initially go unnoticed, they might cause serious financial losses in the long term. That said, the inability to manage data integrity issues properly can lead to a wide range of implications on the integrity of financial transactions and systems. Let’s look at these in the following subsections.

Lack of trust in systems

In order to properly make informed business decisions based on reports and numbers, the financial data used for the reports needs to be as accurate as possible. When decision-makers encounter discrepancies in the reports generated using the data stored in an organization’s internal systems, they lose their trust and confidence in these systems and databases.

At the same time, when customers encounter inconsistencies in their financial statements, accounts, or transactions, they lose trust in the financial institution’s ability to manage their accounts and personal data effectively. This not only damages the institution’s reputation, but it also leads to the loss of customers. That said, taking care of the integrity of financial data is essential not only for internal decision-making but also for securing customers’ trust as well.

Damage to reputation

If not addressed, data integrity issues can significantly harm an organization’s reputation after an incident. Continuing the story where the bank’s customers were affected by erroneous duplicate transactions, even if the data integrity issue was resolved after a few days, there were a lot of social media posts from customers wanting to move their accounts to another bank.

Important note

Unfortunately, all it takes is a single incident to negatively impact the trust and confidence customers have in a company that it has worked hard to build over a long time.

Financial impact

Data integrity issues can lead to errors and discrepancies in financial reports and documents that detail an organization’s financial performance and position. This in turn could negatively impact the organization’s revenue and income.

Note

In Chapter 2

, Avoiding Common Data Integrity Issues and Challenges in Finance Teams, we will discuss how a transaction coding error in one of the world’s biggest banks failed to capture the complete threshold transaction reports from its intelligent deposit machines (IDMs), which led to significant financial penalties for the company.

Compliance issues with laws and regulations

In addition to what has been discussed already, data integrity issues can lead to compliance issues with global laws and regulations that have been established to counter fraud and improve the reliability of financial reporting. Included in this list are the Sarbanes-Oxley Act (SOX), Basel III, and even the General Data Protection Regulation (GDPR), all of which mandate strict data management and protection standards to ensure integrity, transparency, and accountability in financial practices. Non-compliance with these regulations can result in significant financial penalties that can negatively impact an organization’s financial health and public image.

At this point, you should have a better appreciation of why financial data integrity management is important. In the next section, we’ll discuss various concepts relevant to data integrity management to prepare us for the succeeding chapters in this book.

A quick tour of concepts relevant to data integrity management

Making better business decisions relies on having accurate and trustworthy financial data. To help us get started, we’ll begin with several foundational concepts, which will be essential in understanding the topics in later chapters.

Levenshtein distance

With companies often dealing with transactions and records from multiple sources, utilizing string similarity algorithms such as the Levenshtein distance can help reconcile these datasets by matching similar entries especially when there are issues finding the exact match due to typos or minor discrepancies.

The Levenshtein distance, invented by Vladimir Levenshtein, measures the similarity between two strings by counting the number of edits needed to transform one word into another. It quantifies this similarity in terms of inserting, deleting, or substituting characters required for the conversion. Let us take a simple example between health and wealth. The