Saturday, November 23, 2019
Python YouTube channel!
Hey everyone! I’ve started a YouTube channel under the name Pythonic Accountant, and will be posting short format videos to help anyone interested get started using python for accounting. To start I’ll show some basic things that I would have found useful when starting out with python, and later I’ll probably delve into whatever I find interesting, or if people start asking for specific things. Please take a look and provide feedback! And if you like the videos, please subscribe so you can stay updated with new videos, and to show your support!
Check out the Pythonic Accountant YouTube channel here!
Monday, April 8, 2019
AICPA + Python = Awesome
AICPA has taken a great step toward exploring open source and Python in the world of audit. I helped the Audit Data Analytics working group record a video on how Python can improve the audit process, and it's up on their Audit Data Standards website!
The video quickly walks through how you could use Python to import a trial balance and general ledger, do some basic validations, and then perform some journal entry analyses.
The video is posted here - it's the second video on the page.
I also helped write up a document that goes with the video.
The github repo is here. It's still in need of some work, so any contributions would be greatly appreciated!
Good job AICPA!
The video quickly walks through how you could use Python to import a trial balance and general ledger, do some basic validations, and then perform some journal entry analyses.
I also helped write up a document that goes with the video.
The github repo is here. It's still in need of some work, so any contributions would be greatly appreciated!
Good job AICPA!
Sunday, December 2, 2018
Extracting Accounting Data from PDFs (Part 1)
Extracting tabular data from PDF files¶
I decided to do a few posts on extracting data from PDF files. The series will go over extracting table-like data from PDF files specifically, and will show a few options for easily getting data into a format that's useful from an accounting perspective. Specifically, in this post, we'll look at tabular data that is mostly structured, and is computer generated. The only time you would want to be extracting data from a PDF file is when you cannot obtain the data in another format. If you can get any other format, such as CSV, tab-delimited, excel, etc then you should get that format instead and import with several much easier methods. The PDF parsing is not very easy, but at least with Python it becomes a lot easier than it otherwise would be.
There are basically two ways to use pdfplumber to extract text in a useful format from PDF files. One is using the extract_table or extract_tables methods, which finds and extracts tables as long as they are formatted easily enough for the code to understand where the parts of the table are. They can be tricky though, when words don't line up right. In those cases, the extract_text method might work better, combined with the parse library to parse the elements of each row as needed. We'll briefly show extract_text here, but stay tuned for a more in depth explanation and example of the extract_text in a future post.
Let's get started!¶
First we will import the pandas and pdfplumber libraries. These do not come with standard python, and will need to be installed using pip, by typing pip install pandas pdfplumber within the command prompt.
Note: The recommended way to write programs and use pip install is within a virtual environment, rather than within base Python, but let's save that for a later lesson.
Import needed libraries¶
In [1]:
import pandas as pd
import numpy as np
import pdfplumber
AR Aging Report - PDF to Python¶
In this example, we are using a PDF sample AR aging report I found on this webpage (it's the second link, which can be accessed here). We'll use pdfplumber to pull in the table and then use Pandas to convert the data, foot it, and perform a simple aging analysis.
In [2]:
# Set the location of the pdf file
pdf_file = 'data/ARbyCustDetailCompany.pdf'
Load the file in pdfplumber¶
Let's load the file, then see what the text looks like in the first group of rows
In [3]:
with pdfplumber.open(pdf_file) as pdf:
first_page = pdf.pages[0]
rows = first_page.extract_text().split('\n')
rows[:10]
Out[3]:
Let's view the image, checking to see how it has identified the various word in the page¶
Here we are converting the PDF to an image with outlines on each word, so we can tell what pdfplumber is considering "words". This will be useful for our next step, which will be to use words on the page to identify the edges of the table we want to crop.
Note - you will need to install two libraries to get the image creation working with pdfplumber: ImageMagick (must be version 6.9 or earlier) and Ghostscript.
In [4]:
first_page.to_image(resolution=150).outline_words()
Out[4]:
This looks like the right data, but because of the varying and inconsistent layout of each row, for this PDF, the extract_table should be easier than parsing it manually. The extract_table works best if you crop the PDF to just the table, so we will do that next.
Crop the Main Table¶
We will crop the table by identifying the locations of various corner text pieces to use to build a box around the table we want to grab.
In [5]:
with pdfplumber.open(pdf_file) as pdf:
first_page = pdf.pages[0]
rows = first_page.extract_words()
for row in rows:
if row['text'] == 'Name/Company':
x0 = row['x0']
top = row['top']
if row['text'] == 'Watson':
bottom = row['bottom']
if row['text'] == 'ST':
x1 = row['x1']
box = (x0, top, x1, bottom)
box
Out[5]:
In [6]:
# Now we can crop the page starting with Name/Company for our upper and left bound, to right of 'ST' and bottom of "Watson"
with pdfplumber.open(pdf_file) as pdf:
first_page = pdf.pages[0]
page = first_page.crop(bbox=(box)) # (x0, top, x1, bottom)
table = page.extract_table(table_settings={
"vertical_strategy": "text",
"horizontal_strategy": "text",
})
In [7]:
# Let's take a look at the cropped table
page.to_image(resolution=150)
Out[7]:
In [8]:
# Ok- let's see how this is looking data-wise
for row in table[:8]:
print(row)
Nice progress, but a few issues¶
- Blank rows
['', '', ''...] - Funky columns
'61-90 Ov', 'er 90' - Because of how we cut off the table, we're missing part of some of the column names such as 'Not Invoiced' rather than 'Invoiced'
In [9]:
# Fis the blank rows by including only those rows that do not all equal blank strings
table = [row for row in table if ''.join([str(i) for i in row]) != '']
Load into a dataframe and view the first five rows¶
In [10]:
df = pd.DataFrame(table)
df.head()
Out[10]:
Two new issues¶
- Column names are just numbers
- Record 4 is missing the name. We can't pivot unless all fields are filled out, so we need to fill down here.
In [11]:
# Update the column names
columns = df.iloc[0]
columns
Out[11]:
In [12]:
columns[7], columns[10], columns[11] = 'Not Invoiced', '61-90', 'Over 90'
Set the column names and drop the first row.¶
In [13]:
df.columns = columns
df = df.drop(0)
df.head()
Out[13]:
Before we move on....¶
We should check the data types - the columns with numbers should be 'float' or 'int', and if they are not we will need to convert them. This will cause problems if we do any math, which we need to do, because strings behave very differently than numbers. See example below
In [14]:
print('1' + '5') # formatted as string
print(1 + 5) # formatted as ints
In [15]:
df.info()
Looks like we need to fix this¶
It looks like the numbers are comign in as "object", which means they'll be treated as strings. No good. We can fix using a function that converts to float.
In [16]:
def convert_to_float(num):
try:
return float(num.replace(',',''))
except:
return 0
In [17]:
for col in ['Not Invoiced', 'Current', '31-60', '61-90', 'Over 90']:
df[col] = df[col].map(convert_to_float)
df.info()
Good! Now, let's only include columns we need¶
In [18]:
df = df[['Name/Company Telephone', 'Not Invoiced', 'Current', '31-60', '61-90', 'Over 90']]
df
Out[18]:
Looking good! Let's drop-down the names to empty records so we can properly create a pivot table¶
In [19]:
df['Name/Company Telephone'] = df[['Name/Company Telephone']].replace('', np.nan).ffill()
df.head(8)
Out[19]:
Let's look at a pivot table by sum of values on Name¶
In [20]:
pivot = df.pivot_table(index='Name/Company Telephone', aggfunc=sum)
In [21]:
pivot
Out[21]:
The columns are out of order. No prob, let's feed in the correct order.¶
In [22]:
pivot = pivot[['Not Invoiced', 'Current', '31-60', '61-90', 'Over 90']]
pivot
Out[22]:
Now we have a DataFrame that looks like an AR Aging! Let's check the totals against the report.¶
In [23]:
with pdfplumber.open(pdf_file) as pdf:
first_page = pdf.pages[0]
text = first_page.extract_text().split('\n')
totals_str = text[-7:-1]
In [24]:
totals = {}
for line in totals_str:
line = line.replace('Total Not Invoiced', 'Not Invoiced')
*key, val = line.split()
totals[' '.join(key)] = convert_to_float(val)
totals
Out[24]:
In [25]:
# Totals from report for validation purposes:
def validate_totals(pivot):
pivot_dict = dict(zip(pivot.sum().index, pivot.sum().values))
pivot_dict['Total'] = pivot.sum().sum()
print('Reconciliation:')
for key, val in totals.items():
calc_val = pivot_dict[key]
dif = val - calc_val
print(f'{key.ljust(12)} - expected value of {val}, actual of {calc_val}, difference is {dif}')
validate_totals(pivot)
Something is awry!¶
After some investigation, it looks like we included subtotals lines inadvertantly, so we'll need to exclude those. There are several ways to fix, but easiest to me is use the Qty column early on, if it's blank then we should drop the row.
In [26]:
df = pd.DataFrame(table)
columns = df.iloc[0]
columns[7], columns[10], columns[11] = 'Not Invoiced', '61-90', 'Over 90'
df.columns = columns
df = df.drop(0)
def convert_to_float(num):
try:
return float(num.replace(',',''))
except:
return 0
for col in ['Not Invoiced', 'Current', '31-60', '61-90', 'Over 90']:
df[col] = df[col].map(convert_to_float)
df = df[['Name/Company Telephone', 'Qty', 'Not Invoiced', 'Current', '31-60', '61-90', 'Over 90']]
df['Name/Company Telephone'] = df[['Name/Company Telephone']].replace('', np.nan).ffill()
# Remove columns without Qty
df = df[df['Qty'] != '']
pivot = df.pivot_table(index='Name/Company Telephone', aggfunc=sum)
pivot = pivot[['Not Invoiced', 'Current', '31-60', '61-90', 'Over 90']]
validate_totals(pivot)
Woohoo! Differences are all zero! Now that we've reconciled the AR Aging, we can look at what the actual aging shows.¶
In [27]:
pivot['Total']= pivot.sum(axis=1)
In [28]:
pivot.loc['Total'] = pivot.sum()
In [29]:
pivot.loc['Perc'] = round(pivot.loc['Total'] / pivot.loc['Total'].sum() * 200, 2)
In [30]:
pivot
Out[30]:
What now?¶
From here, you can do a few things.
- You can export this dataframe to excel or csv and perform any standard tests or calculations you would perform on a regular spreadsheet version of an AR aging, such as exploring an unusual higher percentage of older AR, any negative AR, related parties, etc.
- You can automate this process, assuming the data will always be in the same format.
For now... let's see what a csv output looks like...
In [31]:
pivot.to_csv('test.csv')
In [32]:
with open('test.csv') as f:
print(f.read())
Stay tuned for the next one, and as always let me know if you have any questions or issues understanding what I've done here!
-Dan
Subscribe to:
Posts (Atom)