AICPA-AuditDataAnalytics2018 · danshorstein · Jan 18, 2020 · Jan 14, 2020 · Jan 14, 2020 · Jan 14, 2020
diff --git a/README.md b/README.md
@@ -2,22 +2,43 @@
 
 This repository displays the benefits of using Python and the Jupyter Notebook in a financial statement audit.
 
+## Requirements
+
+If you haven't already installed Python 3 and Jupyter, the easiest way to install both is by using [Anaconda](https://www.anaconda.com/distribution/).
+
 ## Usage
 
 TODO: Write usage instructions
+1. [How to reshape SAP Data for your
+   audit](https://github.com/AICPA-AuditDataAnalytics2018/ADS---Python-Example-/blob/master/samples/reshape_rename_sap_data.ipynb)
+2. [How to reshape Quickbooks General Ledger Data for your audit](https://github.com/AICPA-AuditDataAnalytics2018/ADS---Python-Example-/tree/master/samples)
+3. [How to split a DataFrame (csv, xlsx, other) using pandas and
+   groupby](https://github.com/AICPA-AuditDataAnalytics2018/ADS---Python-Example-/blob/master/samples/Split%20Dataframe%20with%20Groupby.ipynb)
 
 ## Contributing
 
 1. Fork it!
 2. Create your feature branch: `git checkout -b my-new-feature`
-3. Commit your changes: `git commit -am 'Add some feature'`
-4. Push to the branch: `git push origin my-new-feature`
-5. Submit a pull request :D
+3. Add your changes: `git add *`
+4. Commit your changes: `git commit -am 'Add some feature'`
+5. Push to the branch: `git push origin my-new-feature`
+6. Submit a pull request :D
 
 ## History
 
 TODO: Write history
 
+** Consider adding an __init__ method to Test_Procedures, to reduce data entry:
+```python
+def __init__(self, GL_Detail, Log_File=None, JE_Column=None, Output=None):
+   # Checks to make sure data is valid
+   assert JE_Column in GL_Detail.columns
+   self.GL_Detail = GL_Detail
+   ...
+def run():
+   # Execute all procedures in module
+```
+
 ## Credits
 
 TODO: Write credits

diff --git a/requirements.txt b/requirements.txt
@@ -1,2 +1,4 @@
 pandas
-numpy
+numpy
+xlrd
+xlsxwriter
diff --git a/samples/Test_Procedures.py b/samples/Test_Procedures.py
@@ -1,49 +1,75 @@
-# TODO - refactor to clean up and document better
+import csv
+import pandas as pd
+import numpy as np
+
+# Decorator for printing function results. We return a results value to enable
+# automated testing of the methods upon refactoring.
+
+def output_decorator(func):
+    def inner(*args, **kwargs):
+        print(f'{func.__name__} is now started')
+        t = func(*args, **kwargs)
+        print(f'{t["results"]} instances detected')
+        print(f'Results saved at {t["output"]}')
+        return t["results"]
+    return inner
+
 
 class Test_1_Procedures:
+
     # 3.1.1 - Test 1.1 Check for gaps in journal entry numbers
-    def check_for_gaps_in_JE_ID(GL_Detail_YYYYMMDD_YYYYMMDD):
-        print('Checking for gaps in Journal Entry IDs is started')
-        from collections import deque
-        import csv
-        writer = csv.writer(open("Output_Folder/Test_3_1_1_check_for_gaps_in_JE_ID.csv", 'w'))
-        je_nums = deque(maxlen=2)
+    # This method assumes JE's are already sorted in ascending order
+
+    @output_decorator
+    def check_for_gaps_in_JE_ID(GL_Detail,
+                                Journal_ID_Column = 'Journal_ID',
+                                output_file = 'Output_Folder/Test_3_1_1_check_for_gaps_in_JE_ID.csv'):        
         gaps = []
-        for item in GL_Detail_YYYYMMDD_YYYYMMDD['Journal_ID']:
-            je_nums.append(item)
-            if len(je_nums) == 1:
-                continue
-            if je_nums[1] - je_nums[0] > 1:
-                writer.writerow(['Gap identified! {} is followed by {}'.format(*je_nums)])
-                gaps.append(list(je_nums))
-
-        writer.writerow(['Test Results:']) 
-        writer.writerow(['Total of {} gaps found'.format(len(gaps))])
-        print('%d instances detected' %len(gaps))
-        print('Results saved at Output_Folder/Test_3_1_1_check_for_gaps_in_JE_ID.csv')
+        previous = None
+
+        # Loop through each Journal ID, compare to previous
+        for item in GL_Detail[Journal_ID_Column]:
+            if previous and (item - previous > 1):
+                gaps.append([previous, item])
+            previous = item      
+
+        # Write results to the output csv file, set output_file = None for no
+        # output_file.
+        if output_file:
+            with open(output_file, 'w') as file:
+                writer = csv.writer(file)
+                writer.writerow([f'Gap identified! Start gap number is followed by end gap number'])
+                writer.writerows(gaps)
+                writer.writerow(['Test Results:']) 
+                writer.writerow([f'Total of {len(gaps)} gaps found'])
+
+        return ({"results":len(gaps), "output":output_file})
 
 
     # 3.1.2 Compare listing of journal entry numbers from system to log file
-    def comparison_of_entries_of_GL_and_log_file(GL_Detail_YYYYMMDD_YYYYMMDD, Log_File_YYYYMMDD_YYYYMMDD):
-        print('Comparison of entries in General Ledger and Log File is  for gaps in Journal Entry IDs is started')
-        import csv
-        writer = csv.writer(open("Output_Folder/Test_3_1_2_Comparison_of_Entries_of_GL_and_Log_File.csv", 'w'))
+    @output_decorator
+    def comparison_of_entries_of_GL_and_log_file(GL_Detail_YYYYMMDD_YYYYMMDD,
+            Log_File_YYYYMMDD_YYYYMMDD, output_file = "Output_Folder/Test_3_1_2_Comparison_of_Entries_of_GL_and_Log_File.csv"):
+
         In_GL_not_in_LOG = set(GL_Detail_YYYYMMDD_YYYYMMDD['Journal_ID']) - set(Log_File_YYYYMMDD_YYYYMMDD['Journal_ID'])
         In_LOG_not_in_GL = set(Log_File_YYYYMMDD_YYYYMMDD['Journal_ID']) - set(GL_Detail_YYYYMMDD_YYYYMMDD['Journal_ID'])
-        writer.writerow(['Following %a journal entries exist in General Ledger, but missing from the Log File:'
-                         %(len(In_GL_not_in_LOG))])
-        writer.writerow(list(In_GL_not_in_LOG))
-        writer.writerow(['------------------------------------------------------------------------------------'])
-        writer.writerow(['Amounts of following %a journal entries do not match their amounts in Log File:'
+
+        if output_file:
+            with open(output_file, 'w') as file:
+                writer = csv.writer(file)
+                writer.writerow(['Following %a journal entries exist in General Ledger, but missing from the Log File:'
+                             %(len(In_GL_not_in_LOG))])
+                writer.writerow(list(In_GL_not_in_LOG))
+                writer.writerow(['-'*85])
+                writer.writerow(['Amounts of following %a journal entries do not match their amounts in Log File:'
                          %(len(In_LOG_not_in_GL))])
-        writer.writerow(list(In_LOG_not_in_GL))
-        print('%d instances detected' %(len(In_GL_not_in_LOG) + len(In_LOG_not_in_GL)))
-        print('Results saved at Output_Folder/Test_3_1_2_Comparison_of_Entries_of_GL_and_Log_File.csv')
-
+                writer.writerow(list(In_LOG_not_in_GL))
+        return ({"results": (len(In_LOG_not_in_GL) + len(In_GL_not_in_LOG)),
+                "output": output_file})
 
     # 3.1.3 Test 1.3 Compare total debit amounts and credit amounts of journal entries to system control totals by entry type
     def comparison_of_amounts_of_GL_and_log_file(GL_Detail_YYYYMMDD_YYYYMMDD, Log_File_YYYYMMDD_YYYYMMDD):
-        print('Comparison of amounts of entries in General Ledger and Log File is  for gaps in Journal Entry IDs is started')
+
         gl_totals_pivot = GL_Detail_YYYYMMDD_YYYYMMDD.pivot_table(index=['Journal_ID', 'Amount_Credit_Debit_Indicator'], 
                       values='Net', 
                       aggfunc=sum).reset_index()
@@ -53,22 +79,26 @@ def comparison_of_amounts_of_GL_and_log_file(GL_Detail_YYYYMMDD_YYYYMMDD, Log_Fi
         recon_gl_to_log = recon_gl_to_log.drop('Entered_Date', axis=1)
         recon_gl_to_log = recon_gl_to_log.drop('Entered_Time', axis=1)
         failed_test = recon_gl_to_log.loc[recon_gl_to_log['Comparison'] != 0]
-        failed_test.to_csv('Output_Folder/Test_3_1_3_comparison_of_amounts_of_GL_and_log_file.csv')
-        print('%d instances detected' %len(failed_test['Journal_ID']))
-        print('Results saved at Output_Folder/Test_3_1_3_comparison_of_amounts_of_GL_and_log_file.csv')
+
+        if output_file:
+            failed_test.to_csv('Output_Folder/Test_3_1_3_comparison_of_amounts_of_GL_and_log_file.csv')
+
+        return ({"results": len(In_LOG_not_in_GL), "output": output_file})
 
 class Test_2_Procedures:    
     # 3.2.1 - Examine population for missing or incomplete journal entries
     # Pivot by Journal_ID and make sure Net is 0 for each Journal ID, to check if debits and credits are equal for each entry
-    def check_for_incomplete_entries(GL_Detail_YYYYMMDD_YYYYMMDD):
-        import pandas as pd
-        print('Checking for Incomplete Entries is started')
-        GL_Pivot = GL_Detail_YYYYMMDD_YYYYMMDD.pivot_table(index='Journal_ID', values='Net', aggfunc=sum)
+    def check_for_incomplete_entries(GL_Detail_YYYYMMDD_YYYYMMDD,
+    output_file='', Journal_ID_Column = 'Journal_ID'):
+
+        GL_Pivot = GL_Detail_YYYYMMDD_YYYYMMDD.pivot_table(index=Journal_ID_Column, values='Net', aggfunc=sum)
         failed_test = GL_Pivot.loc[round(GL_Pivot['Net'], 2) != 0]
         failed_test = pd.DataFrame(failed_test.to_records())
-        failed_test.to_csv('Output_Folder/Test_3_2_1_check_for_incomplete_entries.csv')
-        print('%d instances detected' %len(failed_test['Journal_ID']))
-        print('Results saved at Output_Folder/Test_3_2_1_check_for_incomplete_entries.csv')
+
+        if output_file:
+            failed_test.to_csv('Output_Folder/Test_3_2_1_check_for_incomplete_entries.csv')
+
+        return ({"results": len(failed_test[Journal_ID_Column]), "output": output_file})
 
     # 3.2.2 - Examine possible duplicate account entries
     # Check for Journal Entries that have same account and amount in the same period
@@ -131,7 +161,6 @@ def check_for_weekend_entries(GL_Detail_YYYYMMDD_YYYYMMDD):
 
     # Check if Entry Time falls on between 8pm and 6am
     def check_for_nights_entries(GL_Detail_YYYYMMDD_YYYYMMDD):
-        import pandas as pd
         print('Checking for Night Entries is started')
         from datetime import datetime
         GL_Copy = GL_Detail_YYYYMMDD_YYYYMMDD[['Journal_ID', 'Entered_Date', 'Entered_Time']].copy()
@@ -149,8 +178,7 @@ def check_for_nights_entries(GL_Detail_YYYYMMDD_YYYYMMDD):
 
     #Check for individuals who posted 10 or fewer entries and identify entries made by these individuals
     def check_for_rare_users(GL_Detail_YYYYMMDD_YYYYMMDD):
-        import pandas as pd
-        import numpy as np
+
         print('Checking for Rare Users is started')
         GL_Pivot = GL_Detail_YYYYMMDD_YYYYMMDD.pivot_table(index=['Entered_By'], values='Journal_ID', 
                                                            aggfunc=np.count_nonzero).fillna(0)
@@ -164,8 +192,7 @@ def check_for_rare_users(GL_Detail_YYYYMMDD_YYYYMMDD):
 
     # Check for accounts that were used 3 or fewer times and identify entries made to these accounts
     def check_for_rare_accounts(GL_Detail_YYYYMMDD_YYYYMMDD):
-        import pandas as pd
-        import numpy as np
+
         print('Checking for Rare Accounts is started')
         GL_Pivot = GL_Detail_YYYYMMDD_YYYYMMDD.pivot_table(index=['GL_Account_Number'], values='Journal_ID', 
                                                             aggfunc=np.count_nonzero).fillna(0)
@@ -175,4 +202,4 @@ def check_for_rare_accounts(GL_Detail_YYYYMMDD_YYYYMMDD):
         failed_test = GL_Copy.merge(Rare_Accounts, on = ['GL_Account_Number'], how = 'right').fillna(0)
         failed_test.to_csv('Output_Folder/Test_3_2_6.2_check_for_rare_accounts.csv')
         print('%d instances detected' %len(failed_test['GL_Account_Number']))
-        print('Results saved at Output_Folder/Test_3_2_6.2_check_for_rare_accounts.csv')
+        print('Results saved at Output_Folder/Test_3_2_6.2_check_for_rare_accounts.csv')
diff --git a/samples/__pycache__/Test_Procedures.cpython-38.pyc b/samples/__pycache__/Test_Procedures.cpython-38.pyc
diff --git a/samples/quickbooksGLtoDatabase.py b/samples/quickbooksGLtoDatabase.py
@@ -0,0 +1,88 @@
+""" open General Ledger from Excel, reformat into a database. """
+import pandas as pd
+import numpy as np
+import argparse
+from itertools import cycle
+from time import sleep
+
+
+columnNames = ["Account Description", "Type", "Date", "Num", "Name",
+            "Memo", "Split", "Amount", "Balance"]
+
+
+def format_file(df, columnNames=columnNames, columnsToBeMerged = [0,1,2,3,4]):
+
+    df['Account Description'] = merge_columns(df[columnsToBeMerged])
+
+    # clean up empty columns/rows
+    df = df.drop(0)
+    df = df.drop(columnsToBeMerged, axis=1)
+    df = df.dropna(axis=0, thresh=6)
+    df = df.dropna(axis=1, how='all')
+
+    # Reindex Columns, move last column to first position
+    cols = df.columns.tolist()
+    df = df[cols[-1:] + cols[:-1]]
+    df.columns = columnNames
+
+    return df
+
+
+def merge_columns(df):
+
+    # Create single column from DataFrame argument. Value in the merged column
+    # will be the first non-NaN value encountered in the row. If the entire row
+    # is NaN, it will fill using the previous value in the merged column.
+
+    temp_df = df.copy()
+    temp_df = temp_df.replace(' ', np.nan)
+    temp_df["mergeColumn"] = [np.nan for _ in df.index]
+
+    for column in temp_df.columns:
+        temp_df["mergeColumn"] = temp_df["mergeColumn"].fillna(temp_df[column])
+
+    return temp_df["mergeColumn"].fillna(method='ffill')
+
+
+def open_file(filename):
+
+    # try to open filename as Pandas DataFrame, if error, quit.
+    try:
+        df = pd.read_excel(filename, index_col=None, header=None)
+        return df
+    except:
+        print("Error with filename")
+        quit()
+
+# progress bar. Unnecessary for functionality and can be omitted.	
+def progress(percent=0, width=30):
+    left = width * percent // 100
+    right = width - left
+    print('\r[', '#' * left, ' ' * right, ']',
+          f' {percent:.0f}%',
+          sep='', end='', flush=True)
+
+
+def main(file_location):
+
+    file = format_file(open_file(file_location))
+    print(f"Reformatting {file_location} in progress.")
+    for i in range(101):
+    	progress(i)
+    	sleep(0.01)
+    # consider using Path object for file location, to allow for
+    # more accurate location saving.
+    file.to_csv("modified_GL.csv", index=False)
+    print("")
+    print("File Successfully converted.")
+    return file
+
+
+if __name__ == "__main__":
+
+    parser = argparse.ArgumentParser(
+                description='Location of Excel File to be formatted.')
+    parser.add_argument('file_location', type=str, help='file location')
+    args = parser.parse_args()
+
+    main(args.file_location)
diff --git a/samples/test.csv b/samples/test.csv
@@ -0,0 +1,6 @@
+Gap identified! Start gap number is followed by end gap number
+2,4
+5,7
+7,9
+Test Results:
+Total of 3 gaps found