Using Python & Plotly in M&A Due Diligence

The M&A due diligence process differs from deal to deal, however there are certain analyses and data clean-up tasks that are repetitive. One of the more annoying tasks occurs when a company is using QuickBooks.

Many people can attest that QB report extracts are formatted in a manner that makes analyses difficult without data clean-up. Below is a sample export of the ‘Transaction Details by Account’ report in QB.

In the following code I will explain how to clean the transaction details into a usable format for analyses. The below snippet reads the csv file into a Pandas dataframe.

# function to read in the csv file
def read_file(file_path):
if file_path.endswith('.csv'):
try:
df =pd.read_csv(file_path,dtype={'Unnamed: 0 ': 'float'})
except:
df = pd.read_csv(file_path,encoding='ISO-8859-1',dtype={'Unnamed: 0 ': 'float'})
else:
df = pd.read_excel(file_path,skiprows=4)
df.rename(columns={'Unnamed: 0':'Account'}, inplace = True)
return df

The next step takes the dataframe and performs the various data cleaning procedures.

# formats the numeric values
def amount_format(val):
try:
return float(val)
except:
clean_val = val.replace(',','')
clean_val = clean_val.replace('$','')
return float(clean_val)
# fill in the account name and remove white spaces from name
df[‘clean_account’] = df.Account.fillna(method = ‘ffill’)
df[‘clean_account’]=df[‘clean_account’].apply(lambda x: x.strip())
df = df.drop(‘Account’, axis =1)
# format numeric values
df[‘clean_amount’] = df[‘Amount’].apply(amount_format)
# drop redundant data and rows with totals
df = df[~df[‘clean_account’].str.startswith(‘Total’)]
df = df.dropna(subset=[‘clean_amount’])
df.drop([‘Balance’,’Amount’],axis =1, inplace=True)
# format dates
df[‘Date’].fillna(0, inplace=True)
df[‘clean_date’] = df[‘Date’].apply(lambda x: pd.to_datetime(x))
df.drop(‘Date’, axis=1, inplace=True)
df[‘end_period’] = df[‘clean_date’].apply(lambda x: x.to_period(‘M’).to_timestamp(‘M’))
df[‘end_period’] = df[‘end_period’].dt.date
df[‘year’] = df[‘end_period’].apply(lambda x: x.year)
# fill null values
df[‘Num’].fillna(‘None’, inplace=True)
try:
df[‘Memo/Description’].fillna(‘None’,inplace=True)
except:
df[‘Memo’].fillna(‘None’,inplace=True)
df[‘Name’].fillna(‘None’,inplace=True)

We now have clean transaction details with no blank rows, no null values, and an account associated with every transaction.

Now that we have clean data it’s time to analyze the transactions.

Trending account activity to understand seasonality, relationships, and outliers is a baseline analysis that every diligence advisor performs. One great tool to quickly visualize transactions over time is Plotly Dash (https://dash.plot.ly/getting-started).

Plotly Dash allows you to quickly build customized dashboards that can be used over and over again versus creating new visuals in Excel for every deal. Dash has several built-in graphs and interactive tables that can be used to create diligence dashboards and reduce time spent recreating visuals (see https://dash.plot.ly/dash-core-components).

Below we have identified outlier transactions with a quick visualization tool that can be reused with little effort.

If we integrate the above Python code into our dashboard we can simply upload the ‘Transaction Details by Account’ report into our dashboard, clean the data, and visualize the account trends with the click of a button.

Utilizing tools such as Python and Dash reduce redundancies and expedite the diligence timeline to ensure client needs are met in a timely manner.