Estimated reading time: 40 minutes 
import pandas as pd
## This is good to do for accounting values
pd.options.display.float_format = '{:,.2f}'.format

gl = pd.read_excel(r'data/general-ledger-sample.xlsx')

gl.head()
Type Unnamed: 1 Date Unnamed: 3 Num Unnamed: 5 Adj Unnamed: 7 Name Unnamed: 9 Memo Unnamed: 11 Split Unnamed: 13 Debit Unnamed: 15 Credit Unnamed: 17 Balance
NaN Company Checking Account NaN NaN NaN NaN nan NaT nan NaN nan nan nan NaN nan NaN nan NaN nan nan nan nan nan 12,349.00
NaN NaN NaN NaN Check nan 2018-01-01 nan 5001 nan nan nan Kuver Property nan Janaury Rent nan Rent nan nan nan 583.75 nan 11,765.25
NaN Check nan 2018-01-01 nan 5000 nan nan nan Online Accounting nan Set up QuickBooks file nan Accounting Fees nan nan nan 225.00 nan 11,540.25
NaN Deposit nan 2018-01-01 nan NaN nan nan nan NaN nan Deposit nan -SPLIT- nan 31,349.00 nan nan nan 42,889.25
NaN Check nan 2018-01-05 nan 5002 nan nan nan Deborah Wood (Owner) nan Petty Cash nan Petty Cash Account nan nan nan 500.00 nan 42,389.25

A general ledger (GL) is a set of numbered accounts a business uses to keep track of its financial transactions and to prepare financial reports.

## A parser to fix the above issue. 
def acct_append(row, new_accts):
    if pd.isnull(row[1]):
        new_accts.append(row[0])
    else:
        new_accts.append('{} | {}'.format(*row))


def fix_qb_gl(gl):
    gl = gl.dropna(axis=1, how='all')
    main_acct = list(gl.index.get_level_values(1))
    sub_acct = list(gl.index.get_level_values(2))
    acct = list(zip(main_acct, sub_acct))
    new_accts = []
    acct_append(acct[0], new_accts)
    
    for idx, (m, s) in enumerate(acct[1:]):
        if str(m).startswith('Total'):
            m = 'DELETE'       
        if str(s).startswith('Total'):
            s = 'DELETE'
        idx += 1
        acct[idx] = m, s

        if pd.isnull(m):  # Fill NA if main is NA
            acct[idx] = acct[idx - 1][0], acct[idx][1]
            
            if pd.isnull(s):  # If main is NA, then fill NA if sub is NA
                acct[idx] = acct[idx][0], acct[idx-1][1]

        acct_append(acct[idx], new_accts)  # Create the new acct
    gl = gl.reset_index(drop=True)
    gl['Acct'] = pd.Series(new_accts)
    gl[['Debit', 'Credit']] = gl[['Debit', 'Credit']].fillna(0)
    gl['Net'] = gl.apply(lambda x: (x['Debit'] - x['Credit']
                                    if 'DELETE' not in x['Acct']
                                    else 0), axis=1)
    gl = gl.fillna('NA')
    gl = gl.where(gl['Net'] != 0).dropna()
    columns = ['Acct', 'Type', 'Date', 'Num', 'Name', 'Memo',
               'Split', 'Debit', 'Credit', 'Net']
    gl = gl[columns]
    gl['Date'] = gl['Date'].apply(pd.datetime.date)
    return gl

gl = fix_qb_gl(gl)

For the majority of the transactions, only one side Credit or Debit gets called on. The net effect gets reported on the right. Net is simply the cash inflow and outflow if your accounts are prepared on the cash basis. If your accounts are prepared on the accrual basis.

Account on the left is the accounts that is credited or debited.

Name is the name of the person or company the transaction is associated with. Memo is a note to help improve the visibility of the transaction. Split is the contra account that is also influenced as part of the double ledger accounting system. The split lines are where the balances get set or recalibrated.

len(gl.Name.unique())

111

gl[gl.Acct == 'Inventory Asset'].head(5)
Acct Type Date Num Name Memo Split Debit Credit Net
624 Inventory Asset Bill 2018-01-02 76850-2 Peacock Bulb Manufacturing Company Specialty Bulbs, 100 watt (6 pack) Accounts Payable 28.50 0.00 28.50
625 Inventory Asset Bill 2018-01-02 76850-2 Peacock Bulb Manufacturing Company Cand. Light, 20 watts (8 pack) Accounts Payable 48.00 0.00 48.00
626 Inventory Asset Bill 2018-01-06 87865 Paulsen's Lighting Tiffany Collection,Golden Baroque, 1-light lan... Accounts Payable 2,400.00 0.00 2,400.00
627 Inventory Asset Invoice 2018-01-06 71047 Baker's Professional Lighting:Store #25 Pearl Nickle, 5 Light, Medium Base (100 watt max) Accounts Receivable 0.00 240.00 -240.00
628 Inventory Asset Invoice 2018-01-06 71047 Baker's Professional Lighting:Store #25 Black Finish, Solid Brass, Clear Glass, Hangin... Accounts Receivable 0.00 36.00 -36.00

Now you can run some custom filter commands. For example here you filter the Inventory Asset and also the net amount. Only amounts above 6000 are printed.

gl[gl.Acct == 'Inventory Asset'].where(abs(gl[gl.Acct == 'Inventory Asset'].Net) > 6000).dropna()
Acct Type Date Num Name Memo Split Debit Credit Net
794 Inventory Asset Bill 2018-03-09 R909878 Paulsen's Lighting Golden Umber (100 watt) Six Light Chandelier Accounts Payable 8,500.00 0.00 8,500.00
795 Inventory Asset Bill 2018-03-09 R909878 Paulsen's Lighting Burnished Brass (60 watt) w/Golden Umber Accents Accounts Payable 7,500.00 0.00 7,500.00
1348 Inventory Asset Bill 2018-09-06 6785 Hall Lighting & Accessories Sunset, Clear Glass, 1-100 watt max. Accounts Payable 7,500.00 0.00 7,500.00

Here is an example of filtering between certain dates.

start_date = pd.datetime(2018,1,27).date()
end_date = pd.datetime(2018,1,28).date()
gl.where((start_date <= gl.Date) & (gl.Date <= end_date)).dropna()
Acct Type Date Num Name Memo Split Debit Credit Net
444 Accounts Receivable Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson NA -SPLIT- 854.00 0.00 854.00
445 Accounts Receivable Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 NA -SPLIT- 2,187.45 0.00 2,187.45
446 Accounts Receivable Payment 2018-01-28 254 Miscellaneous - Retail:Alison Johnson 71053 Undeposited Funds 0.00 854.00 -854.00
670 Inventory Asset Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson Tapestry, (2-60 watt Med), Etched Cracked Glas... Accounts Receivable 0.00 30.00 -30.00
671 Inventory Asset Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson Tapestry (8/60 watt) Faux Alabaster Glass Accounts Receivable 0.00 75.00 -75.00
672 Inventory Asset Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson Vianne Lanterns, Satin Antiqued Solid Brass, 3... Accounts Receivable 0.00 193.79 -193.79
673 Inventory Asset Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson White, 4.5x7.5, 1-100 watt Medium Base, Satin ... Accounts Receivable 0.00 30.00 -30.00
674 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Die Cast Lanterns, Black, 1-100 watt, Medium B... Accounts Receivable 0.00 228.00 -228.00
675 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Tapestry, (2-60 watt Med), Etched Cracked Glas... Accounts Receivable 0.00 90.00 -90.00
676 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Pearl Nickle, 5 Light, Medium Base (100 watt max) Accounts Receivable 0.00 160.00 -160.00
677 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Sunset, Clear Glass, 1-100 watt max. Accounts Receivable 0.00 60.00 -60.00
678 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Black Finish, Solid Brass, Clear Glass, Hangin... Accounts Receivable 0.00 54.00 -54.00
679 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Burnished Brass (60 watt) w/Golden Umber Accents Accounts Receivable 0.00 75.00 -75.00
680 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Chestnut (3/60 watt) Marble Glass Accounts Receivable 0.00 95.00 -95.00
681 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Pendant Bar, Textured White, 50lbs max Accounts Receivable 0.00 10.00 -10.00
682 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Athena Collection, (100 watt max) Copper Verde... Accounts Receivable 0.00 32.14 -32.14
683 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Cand. Light, 20 watts (8 pack) Accounts Receivable 0.00 24.00 -24.00
684 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Fluorescent Lamp, T-12, Medium Bipin (30pack) Accounts Receivable 0.00 6.34 -6.34
685 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Fluorescent Lamp, T-5, Bipin Accounts Receivable 0.00 6.00 -6.00
686 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Halogen Lamp, Volts:3.5 Tubular Accounts Receivable 0.00 2.50 -2.50
687 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Specialty Bulbs, 100 watt (6 pack) Accounts Receivable 0.00 9.50 -9.50
688 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Specialty, Stage & Studio Bulbs 60 watt Accounts Receivable 0.00 20.00 -20.00
689 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Flat Glass, (25 watt max) Polished Brass, 4 light Accounts Receivable 0.00 40.00 -40.00
690 Inventory Asset Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Beveled Mirror, Etched Ribbed Glass (75 watt max) Accounts Receivable 0.00 44.00 -44.00
1581 Undeposited Funds Payment 2018-01-28 254 Miscellaneous - Retail:Alison Johnson 71053 Accounts Receivable 854.00 0.00 854.00
1871 QuickBooks Credit Card Credit Card Charge 2018-01-28 1256831 Business Supply Center ink cartridges Supplies 0.00 18.15 -18.15
2913 Revenue | Revenue Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson Tapestry, (2-60 watt Med), Etched Cracked Glas... Accounts Receivable 0.00 90.00 -90.00
2914 Revenue | Revenue Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson Tapestry (8/60 watt) Faux Alabaster Glass Accounts Receivable 0.00 300.00 -300.00
2915 Revenue | Revenue Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson Vianne Lanterns, Satin Antiqued Solid Brass, 3... Accounts Receivable 0.00 400.00 -400.00
2916 Revenue | Revenue Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson White, 4.5x7.5, 1-100 watt Medium Base, Satin ... Accounts Receivable 0.00 64.00 -64.00
... ... ... ... ... ... ... ... ... ... ...
2926 Revenue | Revenue Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Cand. Light, 20 watts (8 pack) Accounts Receivable 0.00 50.40 -50.40
2927 Revenue | Revenue Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Fluorescent Lamp, T-12, Medium Bipin (30pack) Accounts Receivable 0.00 13.50 -13.50
2928 Revenue | Revenue Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Fluorescent Lamp, T-5, Bipin Accounts Receivable 0.00 18.00 -18.00
2929 Revenue | Revenue Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Halogen Lamp, Volts:3.5 Tubular Accounts Receivable 0.00 4.95 -4.95
2930 Revenue | Revenue Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Specialty Bulbs, 100 watt (6 pack) Accounts Receivable 0.00 19.80 -19.80
2931 Revenue | Revenue Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Specialty, Stage & Studio Bulbs 60 watt Accounts Receivable 0.00 46.80 -46.80
2932 Revenue | Revenue Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Flat Glass, (25 watt max) Polished Brass, 4 light Accounts Receivable 0.00 90.00 -90.00
2933 Revenue | Revenue Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Beveled Mirror, Etched Ribbed Glass (75 watt max) Accounts Receivable 0.00 86.40 -86.40
3841 Purchases (Cost of Goods) Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson Tapestry, (2-60 watt Med), Etched Cracked Glas... Accounts Receivable 30.00 0.00 30.00
3842 Purchases (Cost of Goods) Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson Tapestry (8/60 watt) Faux Alabaster Glass Accounts Receivable 75.00 0.00 75.00
3843 Purchases (Cost of Goods) Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson Vianne Lanterns, Satin Antiqued Solid Brass, 3... Accounts Receivable 193.79 0.00 193.79
3844 Purchases (Cost of Goods) Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson White, 4.5x7.5, 1-100 watt Medium Base, Satin ... Accounts Receivable 30.00 0.00 30.00
3845 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Die Cast Lanterns, Black, 1-100 watt, Medium B... Accounts Receivable 228.00 0.00 228.00
3846 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Tapestry, (2-60 watt Med), Etched Cracked Glas... Accounts Receivable 90.00 0.00 90.00
3847 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Pearl Nickle, 5 Light, Medium Base (100 watt max) Accounts Receivable 160.00 0.00 160.00
3848 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Sunset, Clear Glass, 1-100 watt max. Accounts Receivable 60.00 0.00 60.00
3849 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Black Finish, Solid Brass, Clear Glass, Hangin... Accounts Receivable 54.00 0.00 54.00
3850 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Burnished Brass (60 watt) w/Golden Umber Accents Accounts Receivable 75.00 0.00 75.00
3851 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Chestnut (3/60 watt) Marble Glass Accounts Receivable 95.00 0.00 95.00
3852 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Pendant Bar, Textured White, 50lbs max Accounts Receivable 10.00 0.00 10.00
3853 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Athena Collection, (100 watt max) Copper Verde... Accounts Receivable 32.14 0.00 32.14
3854 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Cand. Light, 20 watts (8 pack) Accounts Receivable 24.00 0.00 24.00
3855 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Fluorescent Lamp, T-12, Medium Bipin (30pack) Accounts Receivable 6.34 0.00 6.34
3856 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Fluorescent Lamp, T-5, Bipin Accounts Receivable 6.00 0.00 6.00
3857 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Halogen Lamp, Volts:3.5 Tubular Accounts Receivable 2.50 0.00 2.50
3858 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Specialty Bulbs, 100 watt (6 pack) Accounts Receivable 9.50 0.00 9.50
3859 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Specialty, Stage & Studio Bulbs 60 watt Accounts Receivable 20.00 0.00 20.00
3860 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Flat Glass, (25 watt max) Polished Brass, 4 light Accounts Receivable 40.00 0.00 40.00
3861 Purchases (Cost of Goods) Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 Beveled Mirror, Etched Ribbed Glass (75 watt max) Accounts Receivable 44.00 0.00 44.00
4991 Supplies Credit Card Charge 2018-01-28 1256831 Business Supply Center ink cartridges QuickBooks Credit Card 18.15 0.00 18.15

69 rows × 10 columns

This is simply a pivot table that sums up each unique accounts.

pivot = gl.pivot_table(values=['Debit', 'Credit', 'Net'], index='Acct', aggfunc='sum', margins=True)
accts = list(gl.Acct.unique())
accts.append('All')
pivot.loc[accts]
Credit Debit Net
Acct
Company Checking Account 403,171.13 480,976.45 77,805.32
Petty Cash Account 0.00 500.00 500.00
Accounts Receivable 375,976.45 408,309.53 32,333.08
Inventory Asset 173,360.75 131,301.50 -42,059.25
Prepaids | Prepaid Insurance 6,875.00 6,875.00 0.00
Undeposited Funds 375,976.45 375,976.45 0.00
Computer & Office Equipment 0.00 2,500.00 2,500.00
Accumulated Depreciation 923.04 0.00 -923.04
Accounts Payable 147,728.80 131,031.50 -16,697.30
QuickBooks Credit Card 3,453.60 2,204.48 -1,249.12
Customer Deposits 0.00 3,500.00 3,500.00
Line of Credit 106,208.85 25,000.00 -81,208.85
Payroll Liabilities | Payroll Taxes Payable 14,251.22 11,377.50 -2,873.72
Payroll Liabilities | SEC125 Payable 1,400.00 1,350.00 -50.00
Deborah Wood Equity | Deborah Wood's Time to Jobs 114,450.00 114,450.00 0.00
Deborah Wood Equity | Deborah Wood Draws 0.00 135,000.00 135,000.00
Opening Bal Equity 375.00 0.00 -375.00
Revenue | Revenue 411,809.53 0.00 -411,809.53
Direct Labor | Wages - Sales-Inside 0.00 2,500.00 2,500.00
Direct Labor | Wages - Warehouse 0.00 19,705.00 19,705.00
Freight Costs 0.00 1,810.00 1,810.00
Packaging Materials 0.00 1,752.50 1,752.50
Purchases (Cost of Goods) 69.00 180,030.75 179,961.75
Sales Commission (outside reps) 0.00 3,990.80 3,990.80
Advertising Expense 0.00 2,000.00 2,000.00
Business License & Fees 0.00 710.23 710.23
Car/Truck Expense | Car Lease 0.00 6,756.00 6,756.00
Car/Truck Expense | Gas 0.00 907.64 907.64
Car/Truck Expense | Insurance-Auto 0.00 1,440.00 1,440.00
Car/Truck Expense | Registration & License 0.00 546.00 546.00
Car/Truck Expense | Repairs & Maintenance 0.00 1,700.23 1,700.23
Conferences and Seminars 0.00 4,700.00 4,700.00
Contributions 0.00 2,500.00 2,500.00
Depreciation Expense 0.00 923.04 923.04
Dues and Subscriptions 0.00 1,900.00 1,900.00
Insurance | General Liability Insurance 0.00 2,760.00 2,760.00
Insurance | Owner's Health Insurance 0.00 4,272.00 4,272.00
Insurance | Professional Liability Insuranc 0.00 6,875.00 6,875.00
Insurance | Worker's Compensation 0.00 2,782.08 2,782.08
Maintenance/Janitorial 0.00 2,841.95 2,841.95
Marketing Expense 0.00 4,982.00 4,982.00
Meals and Entertainment 0.00 1,376.35 1,376.35
Office Equipment 0.00 1,100.00 1,100.00
Postage and Delivery 0.00 1,098.00 1,098.00
Professional Fees | Accounting Fees 0.00 2,544.00 2,544.00
Professional Fees | Legal Fees 0.00 600.00 600.00
Professional Fees | Payroll Service Fees 0.00 1,529.24 1,529.24
Promotional Expense 0.00 2,021.00 2,021.00
Rent 0.00 7,005.00 7,005.00
Repairs | Computer Repairs 0.00 390.00 390.00
Supplies 0.00 6,199.36 6,199.36
Telephone 0.00 4,003.44 4,003.44
Travel 0.00 3,452.23 3,452.23
Utilities 0.00 501.59 501.59
Wages | Employee Benefits 0.00 2,253.96 2,253.96
Wages | Payroll Tax Expenses 0.00 4,608.57 4,608.57
Wages | Wages - Office Staff 0.00 6,312.00 6,312.00
Other Expense | Interest Expense 0.00 2,296.45 2,296.45
All 2,136,028.82 2,136,028.82 -0.00

Now of course there is countless other things you can do, the possibilities are endless, I will do three of them, and then give a few more ideas for those who which to further extend this notebook

#aggregation by type. This approach below 

gl.groupby("Type").sum()
Debit Credit Net
Type
Bill 147,802.80 147,802.80 0.00
Bill Pmt -Check 131,031.50 131,031.50 0.00
Check 216,937.78 216,937.78 0.00
Credit Card Charge 3,453.60 3,453.60 0.00
Deposit 375,976.45 375,976.45 -0.00
General Journal 9,006.89 9,006.89 0.00
Inventory Adjust 375.00 375.00 0.00
Invoice 585,165.28 585,165.28 -0.00
Liability Check 12,727.50 12,727.50 0.00
Paycheck 147,575.57 147,575.57 0.00
Payment 375,976.45 375,976.45 0.00
Transfer 130,000.00 130,000.00 0.00

Below is an interesting insight as it seems that their has been some contra accounts that have been mistakly categorised.

len(gl.Acct.unique())

58

len(gl.Split.unique())

49

ar = gl[gl["Acct"]=="Accounts Receivable"]; ar
Acct Type Date Num Name Memo Split Debit Credit Net
438 Accounts Receivable Invoice 2018-01-06 71047 Baker's Professional Lighting:Store #25 NA -SPLIT- 2,381.00 0.00 2,381.00
439 Accounts Receivable Invoice 2018-01-14 71050 Godwin Lighting Depot:Store #202 NA -SPLIT- 7,786.40 0.00 7,786.40
440 Accounts Receivable Invoice 2018-01-17 71055 Miscellaneous - Retail:Ms. Jann Minor NA -SPLIT- 1,084.00 0.00 1,084.00
441 Accounts Receivable Payment 2018-01-17 555 Miscellaneous - Retail:Ms. Jann Minor 71055 Undeposited Funds 0.00 1,084.00 -1,084.00
442 Accounts Receivable Invoice 2018-01-18 71092 Miscellaneous - Retail:Brian Stern NA -SPLIT- 1,126.00 0.00 1,126.00
443 Accounts Receivable Payment 2018-01-18 11124 Miscellaneous - Retail:Brian Stern 71092 Undeposited Funds 0.00 1,126.00 -1,126.00
444 Accounts Receivable Invoice 2018-01-28 71053 Miscellaneous - Retail:Alison Johnson NA -SPLIT- 854.00 0.00 854.00
445 Accounts Receivable Invoice 2018-01-28 71072 Cole Home Builders:Phase 2 - Lot 5 NA -SPLIT- 2,187.45 0.00 2,187.45
446 Accounts Receivable Payment 2018-01-28 254 Miscellaneous - Retail:Alison Johnson 71053 Undeposited Funds 0.00 854.00 -854.00
447 Accounts Receivable Invoice 2018-01-29 71124 Kern Lighting Warehouse:Store #34 NA Revenue 8,400.00 0.00 8,400.00
448 Accounts Receivable Invoice 2018-01-31 71059 Godwin Lighting Depot:Store #303 NA -SPLIT- 1,687.95 0.00 1,687.95
449 Accounts Receivable Invoice 2018-02-01 71121 Kern Lighting Warehouse:Store #34 NA -SPLIT- 6,745.00 0.00 6,745.00
450 Accounts Receivable Payment 2018-02-09 130 Baker's Professional Lighting:Store #25 71047 Undeposited Funds 0.00 2,381.00 -2,381.00
451 Accounts Receivable Invoice 2018-02-10 71051 Cole Home Builders:Phase 2 - Lot 5 NA -SPLIT- 4,364.00 0.00 4,364.00
452 Accounts Receivable Invoice 2018-02-11 71112 Thompson Lighting Stores:Store #15 NA -SPLIT- 3,025.00 0.00 3,025.00
453 Accounts Receivable Invoice 2018-02-12 71088 Cole Home Builders:Phase 1 - Lot 2 NA -SPLIT- 2,187.45 0.00 2,187.45
454 Accounts Receivable Invoice 2018-02-14 71110 Baker's Professional Lighting:Store #05 NA -SPLIT- 2,391.00 0.00 2,391.00
455 Accounts Receivable Payment 2018-02-15 1103 Stern Commercial Contractor's:Walker Properties 71106 Undeposited Funds 0.00 21,330.00 -21,330.00
456 Accounts Receivable Invoice 2018-02-18 71093 Miscellaneous - Retail:Ruth Kuver NA -SPLIT- 1,366.00 0.00 1,366.00
457 Accounts Receivable Payment 2018-02-18 5467 Miscellaneous - Retail:Ruth Kuver 71093 Undeposited Funds 0.00 1,366.00 -1,366.00
458 Accounts Receivable Invoice 2018-02-20 71054 Miscellaneous - Retail:Mrs. Chris Holly NA -SPLIT- 1,300.00 0.00 1,300.00
459 Accounts Receivable Payment 2018-02-20 305 Miscellaneous - Retail:Mrs. Chris Holly 71054 Undeposited Funds 0.00 1,300.00 -1,300.00
460 Accounts Receivable Invoice 2018-02-23 71094 Miscellaneous - Retail:David Lo NA -SPLIT- 1,932.00 0.00 1,932.00
461 Accounts Receivable Payment 2018-02-23 12357 Miscellaneous - Retail:David Lo 71094 Undeposited Funds 0.00 1,932.00 -1,932.00
462 Accounts Receivable Invoice 2018-02-27 71052 Baker's Professional Lighting:Store #15 NA -SPLIT- 1,885.00 0.00 1,885.00
463 Accounts Receivable Invoice 2018-02-28 71122 Lavery Lighting & Design:Store #JL-08 NA Revenue 600.00 0.00 600.00
464 Accounts Receivable Payment 2018-02-28 57702 Godwin Lighting Depot:Store #202 71050 Undeposited Funds 0.00 7,786.40 -7,786.40
465 Accounts Receivable Payment 2018-02-28 57713 Godwin Lighting Depot:Store #303 71059 Undeposited Funds 0.00 1,687.95 -1,687.95
466 Accounts Receivable Payment 2018-03-02 5749 Kern Lighting Warehouse:Store #34 71124 Undeposited Funds 0.00 8,400.00 -8,400.00
467 Accounts Receivable Payment 2018-03-02 5750 Kern Lighting Warehouse:Store #13 71123 Undeposited Funds 0.00 110.00 -110.00
... ... ... ... ... ... ... ... ... ... ...
590 Accounts Receivable Invoice 2018-11-05 71117 Lavery Lighting & Design:Store #JL-01 NA -SPLIT- 5,279.00 0.00 5,279.00
591 Accounts Receivable Payment 2018-11-05 101 Miscellaneous - Retail:Valesha Jones 71103 Undeposited Funds 0.00 2,395.00 -2,395.00
592 Accounts Receivable Invoice 2018-11-16 71118 Lavery Lighting & Design:Store #JL-04 NA -SPLIT- 11,715.00 0.00 11,715.00
593 Accounts Receivable Payment 2018-11-16 11270 Cole Home Builders:Phase 1 - Lot 5 71073 Undeposited Funds 0.00 2,138.40 -2,138.40
594 Accounts Receivable Payment 2018-11-16 11257 Cole Home Builders:Phase 1 - Lot 5 71074 Undeposited Funds 0.00 2,138.40 -2,138.40
595 Accounts Receivable Payment 2018-11-16 11250 Cole Home Builders:Phase 1 - Lot 5 71075 Undeposited Funds 0.00 2,138.40 -2,138.40
596 Accounts Receivable Payment 2018-11-16 11280 Cole Home Builders:Phase 2 - Lot 5 71091 Undeposited Funds 0.00 2,138.40 -2,138.40
597 Accounts Receivable Payment 2018-11-16 5930 Kern Lighting Warehouse:Store #13 71131 Undeposited Funds 0.00 5,569.00 -5,569.00
598 Accounts Receivable Invoice 2018-11-20 71104 Miscellaneous - Retail:Melanie Hall NA -SPLIT- 1,600.00 0.00 1,600.00
599 Accounts Receivable Payment 2018-11-20 55449 Miscellaneous - Retail:Melanie Hall 71104 Undeposited Funds 0.00 1,600.00 -1,600.00
600 Accounts Receivable Invoice 2018-11-22 71133 Kern Lighting Warehouse:Store #01 NA -SPLIT- 9,839.00 0.00 9,839.00
601 Accounts Receivable Invoice 2018-11-29 71123 Kern Lighting Warehouse:Store #13 NA -SPLIT- 110.00 0.00 110.00
602 Accounts Receivable Payment 2018-11-29 1052 Stern Commercial Contractor's:Tittle Properties 71107 Undeposited Funds 0.00 17,433.00 -17,433.00
603 Accounts Receivable Payment 2018-11-30 5791 Kern Lighting Warehouse:Store #13 71120 Undeposited Funds 0.00 8,190.00 -8,190.00
604 Accounts Receivable Payment 2018-11-30 1098 Stern Commercial Contractor's:Wilson Suites 71108 Undeposited Funds 0.00 14,355.00 -14,355.00
605 Accounts Receivable Payment 2018-12-01 11311 Lavery Lighting & Design:Store #JL-04 71118 Undeposited Funds 0.00 11,715.00 -11,715.00
606 Accounts Receivable Invoice 2018-12-03 71139 Lavery Lighting & Design:Store #JL-08 Partial ship 12/02/2007 -SPLIT- 2,708.10 0.00 2,708.10
607 Accounts Receivable Invoice 2018-12-07 71138 Baker's Professional Lighting:Store #10 Partial ship 12/7/2007 -SPLIT- 10,680.48 0.00 10,680.48
608 Accounts Receivable Invoice 2018-12-10 71105 Miscellaneous - Retail:Lara Gussman NA -SPLIT- 1,679.00 0.00 1,679.00
609 Accounts Receivable Payment 2018-12-10 66587 Miscellaneous - Retail:Lara Gussman 71105 Undeposited Funds 0.00 1,679.00 -1,679.00
610 Accounts Receivable Payment 2018-12-11 5977 Kern Lighting Warehouse:Store #01 71133 Undeposited Funds 0.00 9,839.00 -9,839.00
611 Accounts Receivable Invoice 2018-12-12 71106 Stern Commercial Contractor's:Walker Properties NA -SPLIT- 21,330.00 0.00 21,330.00
612 Accounts Receivable Payment 2018-12-14 5950 Kern Lighting Warehouse:Store #34 71132 Undeposited Funds 0.00 10,723.60 -10,723.60
613 Accounts Receivable Invoice 2018-12-15 71134 Kern Lighting Warehouse:Store #13 NA -SPLIT- 9,033.50 0.00 9,033.50
614 Accounts Receivable Invoice 2018-12-15 71135 Baker's Professional Lighting:Store #15 NA -SPLIT- 1,422.00 0.00 1,422.00
615 Accounts Receivable Invoice 2018-12-15 71136 Kern Lighting Warehouse:Store #13 NA -SPLIT- 2,222.50 0.00 2,222.50
616 Accounts Receivable Invoice 2018-12-15 71137 Dan A. North Builders:Custom Order - Suite 100A NA -SPLIT- 3,500.00 0.00 3,500.00
617 Accounts Receivable Invoice 2018-12-15 71140 Thompson Lighting Stores:Store #15 NA -SPLIT- 11,800.00 0.00 11,800.00
618 Accounts Receivable Payment 2018-12-15 11301 Lavery Lighting & Design:Store #JL-01 71117 Undeposited Funds 0.00 5,279.00 -5,279.00
619 Accounts Receivable Payment 2018-12-15 5999 Kern Lighting Warehouse:Store #13 71134 Undeposited Funds 0.00 9,033.50 -9,033.50

182 rows × 10 columns

Okay, now this is important, I will further just investigate Accounts Receivable. Here you can see from the below plot the credit side of AR is slightly lower around the zero bound. This showed that there is slightly less amounts or repayment at that value.

ar["Debit"].sum()

408309.53

ar["Credit"].sum()

375976.45

This company has clearly done well, there is a very small amoount of AR outstanding.

%matplotlib inline
import matplotlib.pyplot as plt

ar.hist()

array([[<matplotlib.axes._subplots.AxesSubplot object at 0x115d019b0>,
        <matplotlib.axes._subplots.AxesSubplot object at 0x115d3ad30>],
       [<matplotlib.axes._subplots.AxesSubplot object at 0x115d73c50>,
        <matplotlib.axes._subplots.AxesSubplot object at 0x115dabc50>]],
      dtype=object)

png

Here you can have a look at the values over the period, the numbers below is the daily values. It seems that towards the end of the period they are extending larger amounts of credit.

ar.plot()

<matplotlib.axes._subplots.AxesSubplot at 0x1a17affd68>

png

Now I will create a rolling commulative sum, doing this whe can see if there is any seasonality in the balance.

ar["cum_net"] = ar["Net"].cumsum()

/Users/dereksnow/anaconda/envs/py36/lib/python3.6/site-packages/ipykernel/__main__.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  if __name__ == '__main__':

You can see that the summation remains within range, it is not to bad, there is a slight increase over the period. To prove there is an increase over time, we can run a linear regression to get the slope coefficient.

ar["Date"] =  pd.to_datetime(ar["Date"])

/Users/dereksnow/anaconda/envs/py36/lib/python3.6/site-packages/ipykernel/__main__.py:1: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy
  if __name__ == '__main__':

ar = ar.set_index("Date",drop=True)

ar["Credit"] = - ar["Credit"]



LinregressResult(slope=0.002061083196531608, intercept=32.73900646747864, rvalue=0.5445332646944847, pvalue=1.9277033049221335e-15, stderr=0.00023662564997062874)

ar
Acct Type Num Name Memo Split Debit Credit Net cum_net
Date
2018-01-06 Accounts Receivable Invoice 71047 Baker's Professional Lighting:Store #25 NA -SPLIT- 2,381.00 -0.00 2,381.00 2,381.00
2018-01-14 Accounts Receivable Invoice 71050 Godwin Lighting Depot:Store #202 NA -SPLIT- 7,786.40 -0.00 7,786.40 10,167.40
2018-01-17 Accounts Receivable Invoice 71055 Miscellaneous - Retail:Ms. Jann Minor NA -SPLIT- 1,084.00 -0.00 1,084.00 11,251.40
2018-01-17 Accounts Receivable Payment 555 Miscellaneous - Retail:Ms. Jann Minor 71055 Undeposited Funds 0.00 -1,084.00 -1,084.00 10,167.40
2018-01-18 Accounts Receivable Invoice 71092 Miscellaneous - Retail:Brian Stern NA -SPLIT- 1,126.00 -0.00 1,126.00 11,293.40
2018-01-18 Accounts Receivable Payment 11124 Miscellaneous - Retail:Brian Stern 71092 Undeposited Funds 0.00 -1,126.00 -1,126.00 10,167.40
2018-01-28 Accounts Receivable Invoice 71053 Miscellaneous - Retail:Alison Johnson NA -SPLIT- 854.00 -0.00 854.00 11,021.40
2018-01-28 Accounts Receivable Invoice 71072 Cole Home Builders:Phase 2 - Lot 5 NA -SPLIT- 2,187.45 -0.00 2,187.45 13,208.85
2018-01-28 Accounts Receivable Payment 254 Miscellaneous - Retail:Alison Johnson 71053 Undeposited Funds 0.00 -854.00 -854.00 12,354.85
2018-01-29 Accounts Receivable Invoice 71124 Kern Lighting Warehouse:Store #34 NA Revenue 8,400.00 -0.00 8,400.00 20,754.85
2018-01-31 Accounts Receivable Invoice 71059 Godwin Lighting Depot:Store #303 NA -SPLIT- 1,687.95 -0.00 1,687.95 22,442.80
2018-02-01 Accounts Receivable Invoice 71121 Kern Lighting Warehouse:Store #34 NA -SPLIT- 6,745.00 -0.00 6,745.00 29,187.80
2018-02-09 Accounts Receivable Payment 130 Baker's Professional Lighting:Store #25 71047 Undeposited Funds 0.00 -2,381.00 -2,381.00 26,806.80
2018-02-10 Accounts Receivable Invoice 71051 Cole Home Builders:Phase 2 - Lot 5 NA -SPLIT- 4,364.00 -0.00 4,364.00 31,170.80
2018-02-11 Accounts Receivable Invoice 71112 Thompson Lighting Stores:Store #15 NA -SPLIT- 3,025.00 -0.00 3,025.00 34,195.80
2018-02-12 Accounts Receivable Invoice 71088 Cole Home Builders:Phase 1 - Lot 2 NA -SPLIT- 2,187.45 -0.00 2,187.45 36,383.25
2018-02-14 Accounts Receivable Invoice 71110 Baker's Professional Lighting:Store #05 NA -SPLIT- 2,391.00 -0.00 2,391.00 38,774.25
2018-02-15 Accounts Receivable Payment 1103 Stern Commercial Contractor's:Walker Properties 71106 Undeposited Funds 0.00 -21,330.00 -21,330.00 17,444.25
2018-02-18 Accounts Receivable Invoice 71093 Miscellaneous - Retail:Ruth Kuver NA -SPLIT- 1,366.00 -0.00 1,366.00 18,810.25
2018-02-18 Accounts Receivable Payment 5467 Miscellaneous - Retail:Ruth Kuver 71093 Undeposited Funds 0.00 -1,366.00 -1,366.00 17,444.25
2018-02-20 Accounts Receivable Invoice 71054 Miscellaneous - Retail:Mrs. Chris Holly NA -SPLIT- 1,300.00 -0.00 1,300.00 18,744.25
2018-02-20 Accounts Receivable Payment 305 Miscellaneous - Retail:Mrs. Chris Holly 71054 Undeposited Funds 0.00 -1,300.00 -1,300.00 17,444.25
2018-02-23 Accounts Receivable Invoice 71094 Miscellaneous - Retail:David Lo NA -SPLIT- 1,932.00 -0.00 1,932.00 19,376.25
2018-02-23 Accounts Receivable Payment 12357 Miscellaneous - Retail:David Lo 71094 Undeposited Funds 0.00 -1,932.00 -1,932.00 17,444.25
2018-02-27 Accounts Receivable Invoice 71052 Baker's Professional Lighting:Store #15 NA -SPLIT- 1,885.00 -0.00 1,885.00 19,329.25
2018-02-28 Accounts Receivable Invoice 71122 Lavery Lighting & Design:Store #JL-08 NA Revenue 600.00 -0.00 600.00 19,929.25
2018-02-28 Accounts Receivable Payment 57702 Godwin Lighting Depot:Store #202 71050 Undeposited Funds 0.00 -7,786.40 -7,786.40 12,142.85
2018-02-28 Accounts Receivable Payment 57713 Godwin Lighting Depot:Store #303 71059 Undeposited Funds 0.00 -1,687.95 -1,687.95 10,454.90
2018-03-02 Accounts Receivable Payment 5749 Kern Lighting Warehouse:Store #34 71124 Undeposited Funds 0.00 -8,400.00 -8,400.00 2,054.90
2018-03-02 Accounts Receivable Payment 5750 Kern Lighting Warehouse:Store #13 71123 Undeposited Funds 0.00 -110.00 -110.00 1,944.90
... ... ... ... ... ... ... ... ... ... ...
2018-11-05 Accounts Receivable Invoice 71117 Lavery Lighting & Design:Store #JL-01 NA -SPLIT- 5,279.00 -0.00 5,279.00 51,058.20
2018-11-05 Accounts Receivable Payment 101 Miscellaneous - Retail:Valesha Jones 71103 Undeposited Funds 0.00 -2,395.00 -2,395.00 48,663.20
2018-11-16 Accounts Receivable Invoice 71118 Lavery Lighting & Design:Store #JL-04 NA -SPLIT- 11,715.00 -0.00 11,715.00 60,378.20
2018-11-16 Accounts Receivable Payment 11270 Cole Home Builders:Phase 1 - Lot 5 71073 Undeposited Funds 0.00 -2,138.40 -2,138.40 58,239.80
2018-11-16 Accounts Receivable Payment 11257 Cole Home Builders:Phase 1 - Lot 5 71074 Undeposited Funds 0.00 -2,138.40 -2,138.40 56,101.40
2018-11-16 Accounts Receivable Payment 11250 Cole Home Builders:Phase 1 - Lot 5 71075 Undeposited Funds 0.00 -2,138.40 -2,138.40 53,963.00
2018-11-16 Accounts Receivable Payment 11280 Cole Home Builders:Phase 2 - Lot 5 71091 Undeposited Funds 0.00 -2,138.40 -2,138.40 51,824.60
2018-11-16 Accounts Receivable Payment 5930 Kern Lighting Warehouse:Store #13 71131 Undeposited Funds 0.00 -5,569.00 -5,569.00 46,255.60
2018-11-20 Accounts Receivable Invoice 71104 Miscellaneous - Retail:Melanie Hall NA -SPLIT- 1,600.00 -0.00 1,600.00 47,855.60
2018-11-20 Accounts Receivable Payment 55449 Miscellaneous - Retail:Melanie Hall 71104 Undeposited Funds 0.00 -1,600.00 -1,600.00 46,255.60
2018-11-22 Accounts Receivable Invoice 71133 Kern Lighting Warehouse:Store #01 NA -SPLIT- 9,839.00 -0.00 9,839.00 56,094.60
2018-11-29 Accounts Receivable Invoice 71123 Kern Lighting Warehouse:Store #13 NA -SPLIT- 110.00 -0.00 110.00 56,204.60
2018-11-29 Accounts Receivable Payment 1052 Stern Commercial Contractor's:Tittle Properties 71107 Undeposited Funds 0.00 -17,433.00 -17,433.00 38,771.60
2018-11-30 Accounts Receivable Payment 5791 Kern Lighting Warehouse:Store #13 71120 Undeposited Funds 0.00 -8,190.00 -8,190.00 30,581.60
2018-11-30 Accounts Receivable Payment 1098 Stern Commercial Contractor's:Wilson Suites 71108 Undeposited Funds 0.00 -14,355.00 -14,355.00 16,226.60
2018-12-01 Accounts Receivable Payment 11311 Lavery Lighting & Design:Store #JL-04 71118 Undeposited Funds 0.00 -11,715.00 -11,715.00 4,511.60
2018-12-03 Accounts Receivable Invoice 71139 Lavery Lighting & Design:Store #JL-08 Partial ship 12/02/2007 -SPLIT- 2,708.10 -0.00 2,708.10 7,219.70
2018-12-07 Accounts Receivable Invoice 71138 Baker's Professional Lighting:Store #10 Partial ship 12/7/2007 -SPLIT- 10,680.48 -0.00 10,680.48 17,900.18
2018-12-10 Accounts Receivable Invoice 71105 Miscellaneous - Retail:Lara Gussman NA -SPLIT- 1,679.00 -0.00 1,679.00 19,579.18
2018-12-10 Accounts Receivable Payment 66587 Miscellaneous - Retail:Lara Gussman 71105 Undeposited Funds 0.00 -1,679.00 -1,679.00 17,900.18
2018-12-11 Accounts Receivable Payment 5977 Kern Lighting Warehouse:Store #01 71133 Undeposited Funds 0.00 -9,839.00 -9,839.00 8,061.18
2018-12-12 Accounts Receivable Invoice 71106 Stern Commercial Contractor's:Walker Properties NA -SPLIT- 21,330.00 -0.00 21,330.00 29,391.18
2018-12-14 Accounts Receivable Payment 5950 Kern Lighting Warehouse:Store #34 71132 Undeposited Funds 0.00 -10,723.60 -10,723.60 18,667.58
2018-12-15 Accounts Receivable Invoice 71134 Kern Lighting Warehouse:Store #13 NA -SPLIT- 9,033.50 -0.00 9,033.50 27,701.08
2018-12-15 Accounts Receivable Invoice 71135 Baker's Professional Lighting:Store #15 NA -SPLIT- 1,422.00 -0.00 1,422.00 29,123.08
2018-12-15 Accounts Receivable Invoice 71136 Kern Lighting Warehouse:Store #13 NA -SPLIT- 2,222.50 -0.00 2,222.50 31,345.58
2018-12-15 Accounts Receivable Invoice 71137 Dan A. North Builders:Custom Order - Suite 100A NA -SPLIT- 3,500.00 -0.00 3,500.00 34,845.58
2018-12-15 Accounts Receivable Invoice 71140 Thompson Lighting Stores:Store #15 NA -SPLIT- 11,800.00 -0.00 11,800.00 46,645.58
2018-12-15 Accounts Receivable Payment 11301 Lavery Lighting & Design:Store #JL-01 71117 Undeposited Funds 0.00 -5,279.00 -5,279.00 41,366.58
2018-12-15 Accounts Receivable Payment 5999 Kern Lighting Warehouse:Store #13 71134 Undeposited Funds 0.00 -9,033.50 -9,033.50 32,333.08

182 rows × 10 columns

ar["cum_net"].plot()

<matplotlib.axes._subplots.AxesSubplot at 0x115b63358>

png

ar.head()
Acct Type Date Num Name Memo Split Debit Credit Net cum_net
438 Accounts Receivable Invoice 2018-01-06 71047 Baker's Professional Lighting:Store #25 NA -SPLIT- 2,381.00 0.00 2,381.00 2,381.00
439 Accounts Receivable Invoice 2018-01-14 71050 Godwin Lighting Depot:Store #202 NA -SPLIT- 7,786.40 0.00 7,786.40 10,167.40
440 Accounts Receivable Invoice 2018-01-17 71055 Miscellaneous - Retail:Ms. Jann Minor NA -SPLIT- 1,084.00 0.00 1,084.00 11,251.40
441 Accounts Receivable Payment 2018-01-17 555 Miscellaneous - Retail:Ms. Jann Minor 71055 Undeposited Funds 0.00 1,084.00 -1,084.00 10,167.40
442 Accounts Receivable Invoice 2018-01-18 71092 Miscellaneous - Retail:Brian Stern NA -SPLIT- 1,126.00 0.00 1,126.00 11,293.40 
ar.plot()

<matplotlib.axes._subplots.AxesSubplot at 0x1a17b85f28>

png

from scipy.stats import linregress
linregress(list(ar["cum_net"].values), list(range(len(ar))))


LinregressResult(slope=0.002061083196531608, intercept=32.73900646747864, rvalue=0.5445332646944847, pvalue=1.9277033049221335e-15, stderr=0.00023662564997062874)

ar.head()
Acct Type Num Name Memo Split Debit Credit Net cum_net
Date
2018-01-06 Accounts Receivable Invoice 71047 Baker's Professional Lighting:Store #25 NA -SPLIT- 2,381.00 -0.00 2,381.00 2,381.00
2018-01-14 Accounts Receivable Invoice 71050 Godwin Lighting Depot:Store #202 NA -SPLIT- 7,786.40 -0.00 7,786.40 10,167.40
2018-01-17 Accounts Receivable Invoice 71055 Miscellaneous - Retail:Ms. Jann Minor NA -SPLIT- 1,084.00 -0.00 1,084.00 11,251.40
2018-01-17 Accounts Receivable Payment 555 Miscellaneous - Retail:Ms. Jann Minor 71055 Undeposited Funds 0.00 -1,084.00 -1,084.00 10,167.40
2018-01-18 Accounts Receivable Invoice 71092 Miscellaneous - Retail:Brian Stern NA -SPLIT- 1,126.00 -0.00 1,126.00 11,293.40 
from wordcloud import WordCloud

wordcloud2 = WordCloud().generate(' '.join(ar['Name']))

plt.imshow(wordcloud2)
plt.axis("off")
plt.show()

png

### Here you can focus more on the debtors and look at the debtors aged report.
## https://www.accountingweb.co.uk/tech/tech-pulse/coding-for-accountants-creating-an-aged-debtors-report