Bookkeeping Learned with Python-The Flow of Bookkeeping-

1. Creating journals

【Thing you want to do】 Record the transaction as a ** journal ** in the ** journal **.

[Coding policy] Enter your journals in Pandas DataFrame format into the journal ** df_siwake **. Add and update a row of journal data to ** df_siwake ** by calling a function ** entry ** of the ** Siwake ** class.

Journal rule ①: Describe the account and amount on the debit (left side) and credit (right side)

According to, create a journal ** df_siwake ** as an empty DataFrame for entering journals.

import pandas as pd
df_siwake = pd.DataFrame(index=[],
                         columns=['Journal number', 'date', 'Debit subject', 'Debit amount', 'Credit subject', 'Credit amount'])

For example (Debit) Cash / (Credit) Capital Every time you enter the journal, according to the type of DataFrame

df_siwake.append(pd.Series([1, 20200401, 'cash', 1000, 'Capital', 1000], index=df_kamoku.columns), ignore_index=True)

You can write the code and add a line of DataFrame,

--Cannot enter compound journals (journals that do not have a one-to-one correspondence between debits and credits) ... ① --At the time of input, it is not possible to confirm whether the data type of the journal is correct (such as entering a character string in the debit amount) ... ② --At the time of input, it is not possible to confirm whether the journals are balanced and borrowed ... ③

There is a problem with.

• In the world of bookkeeping, it is very important that ③ is observed. Since the balance is always the same when you enter the journal, you can also make a trial balance later. It is guaranteed that the loan amounts will match.

Therefore, define a class ** Siwake ** for journal entry, and first of all, ① in a form corresponding to the entry of compound journals. Define the function ** entry **.

• Finally, the functions of (2) data type check and (3) balance check will be implemented in ** Siwake **. Since the code will be long, I will add it as a supplement at the end.

class Siwake:
    def __init__(self):
        self.siwake_no = 0

    def entry(self, df, date, kari, kashi): 
        self.siwake_no += 1 # ...Update journal number
            
        for i in range(len(kari)): # ...To support compound journals[Debit item, debit amount]Turn the loop as many times as
            kari_entry = pd.Series([self.siwake_no] + [date] + kari[i] + ["", 0], index=df.columns)          
            df = df.append(kari_entry, ignore_index=True)
            
        for i in range(len(kashi)): # ...To support compound journals[Credit item, credit amount]Turn the loop as many times as
            kashi_entry = pd.Series([self.siwake_no] + [date] + ["", 0] + kashi[i], index=df.columns)
            df = df.append(kashi_entry, ignore_index=True)
            
        return df

Supplement the comments in class ** Siwake **.

In order to record journals in chronological order in the journal, journal number ** siwake_no ** is defined as a class variable. Every time a journal is created (the function entry is called)

self.siwake_no += 1 # ...Update journal number

The journal number is updated as a serial number.

In addition, although this paper deals with only one-line journals for both debit and credit subjects, in practice it is common to create multi-line journals. In order to support compound journals, loop as follows to generate pd.Series for each debit and credit, and then Adding a line to the journal ** df_siwake **.

for i in range(len(kari)): # ...To support compound journals[Debit item, debit amount]Turn the loop as many times as
    kari_entry = pd.Series([self.siwake_no] + [date] + kari[i] + ["", 0], index=df.columns)          
    df = df.append(kari_entry, ignore_index=True)

for i in range(len(kashi)): # ...To support compound journals[Credit item, credit amount]Turn the loop as many times as
    kashi_entry = pd.Series([self.siwake_no] + [date] + ["", 0] + kashi[i], index=df.columns)
    df = df.append(kashi_entry, ignore_index=True)

That concludes the supplementary explanation of class ** Siwake **.

Enter the function ** entry ** in the following format.

siwake.entry(df_siwake:Journal, date:date,
             kari :[[Credit item 1,Debit amount 1], [Credit item 2,Debit amount 2], ... ],
             kashi:[[Credit subject 1,Credit amount 1], [Credit subject 2,Credit amount 2], ... ])

The following line will be added to the journal ** df_siwake **.

Now create an instance of class ** Siwake ** and

siwake = Siwake()

The most basic cycle of company management

1. Establish a company
2. Purchasing products
3. Sale of goods
4. Payment of purchase price
5. Collection of sales price

We will create a journal for each transaction of.

Journal ①: Establish a company

I established a company with 1000 cash.

This source is called ** capital **, and the journal entries are as follows.

df_siwake = siwake.entry(df_siwake, 20200401,
                         [['cash', 1000]],
                         [['Capital', 1000]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]

The cash paid to the company belongs to the company, that is, it is an ** asset **, so it is listed as ** debit **.

On the other hand, the capital belongs to the shareholders who paid 1000 cash (if it is a joint-stock company). It is called ** net worth ** and is listed on ** credit **.

It is important that the debit amount 1000 and the credit amount 1000 always match.

Let's move on a little bit, but the credit's net worth is roughly speaking.

** Capital paid at the time of company establishment + Profit earned after company establishment **

It consists of. Accumulate profits by doing business and return it to shareholders (if it is a joint-stock company) as a dividend, That is the basic cycle of the company.

Since the profits earned after the establishment of the company come to the net assets of the credit,

--Generation of revenue → Increase credit profit → Described in credit --Incurred costs → Reduce credit profit → Described in debit

That is, Journal rule ④: Expenses are debited and revenues are credited Is guided.

Journal ②: Purchasing products

I purchased product 500 from an outside vendor. The journals are as follows.

df_siwake = siwake.entry(df_siwake, 20200402,
                         [['Product', 500]],
                         [['Accounts payable', 500]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]

The purchased goods are from the company, that is, ** assets **, so debit them. (Rule ③: Increase in assets is debited, and increases in liabilities and net assets are credited)

In transactions between companies, the purchase price is paid with the supplier after XX days, etc. Mostly deferred payment. (Of course, you may pay on the spot)

This debt (obligation to pay the price) until payment is called ** accounts payable **.

Accounts payable will be credited to outsiders in the future, that is, they are ** liabilities **. (Rule ③: Increase in assets is debited, and increases in liabilities and net assets are credited)

Journal ③: Selling products

200 of the purchased products were sold at a price of 300. The journal entries for sales are as follows.

df_siwake = siwake.entry(df_siwake, 20200403,
                         [['accounts receivable', 300]],
                         [['Earnings', 300]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]

In transactions between companies, the sales price is agreed with the sales destination, such as collection after XX days. Most of them will be collected at a later date. (Mochiran, it may be collected on the spot)

This receivable (right to receive payment) until collection is called ** accounts receivable **.

Accounts receivable will be the company's cash in the future, that is, they are ** assets **, so they will be credited **. (Rule ③: Increase in assets is debited, and increases in liabilities and net assets are credited)

Sales are ** revenue **, which has the effect of increasing the company's ** net assets (credit) **, so it is listed as ** credit **. (Rule ④: Expenses are debited and revenues are credited)

The journal entries for costs corresponding to sales are as follows.

df_siwake = siwake.entry(df_siwake, 20200403,
                         [['Cost of sales', 200]],
                         [['Product', 200]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]

Selling a product reduces the number of ** products (debits) ** that belong to the company, so list it in the opposite ** credit **. (Rule ③: Increase assets are debited, and increases in liabilities and net assets are credited ⇔ Rule'③: Decrease in assets is credited, and decrease in liabilities and net assets is debited)

As the number of products decreases, the ** cost ** corresponding to the sales of the product is recorded as ** cost of sales **. (Note 2)

Expenses have the effect of reducing the company's ** net worth (credit) **, so the opposite is stated in ** debit **. (Rule ④: Expenses are debited and revenues are credited)

Since a product with a cost of 200 is sold at a selling price of 300, the profit of the transaction is 100.

However, the bookkeeping process does not recognize profits every time you sell a product. The purpose of bookkeeping is ** to calculate profits for a certain period (accounting period) **.

For profits, the total amount of income and expenses for the current period is totaled at the end of the period. By creating a journal entry called ** Profit and Loss Transfer ** for the profit that is the difference, Recognize at once at the end of the accounting period (see below).

Journal ④: Pay the purchase price

We paid an outside contractor for 300 of the 500 purchases. (The remaining 200 are assumed to be paid next month) The journals are as follows.

df_siwake = siwake.entry(df_siwake, 20200420,
                         [['Accounts payable', 300]],
                         [['cash', 300]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]

By paying the purchase price, the company's assets ** cash ** (** debit **) will be reduced. The opposite is stated in ** Credit **. (Rule ③: Increase assets are debited, and increases in liabilities and net assets are credited ⇔ Rule'③: Decrease in assets is credited, and decrease in liabilities and net assets is debited)

On the other hand, ** accounts payable ** (** credit **), which is the debt to pay the supplier, also disappears when the payment is completed. The opposite is stated in ** Debit **. (Rule ③: Increase assets are debited, and increases in liabilities and net assets are credited ⇔ Rule'③: Decrease in assets is credited, and decrease in liabilities and net assets is debited)

Journal ⑤: Collect sales price

The sales price of 200 was collected in cash from the seller. (The remaining 100 are assumed to be collected next month) The journals are as follows.

df_siwake = siwake.entry(df_siwake, 20200430,
                         [['cash', 200]],
                         [['accounts receivable', 200]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]

By collecting the sales price, the company's assets ** cash ** (** debit **) will increase. List in ** Debit **. (Rule ③: Increase in assets is debited, and increases in liabilities and net assets are credited)

On the other hand, ** accounts receivable ** (** debit **), which is a receivable that receives payment from the seller, also disappears when the collection is completed. The opposite is stated in ** Credit **. (Rule ③: Increase assets are debited, and increases in liabilities and net assets are credited ⇔ Rule'③: Decrease in assets is credited, and decrease in liabilities and net assets is debited)

So far

1. Establish a company
2. Purchasing products
3. Sale of goods
4. Payment of purchase price
5. Collection of sales price

I created a series of journals. Let's check the contents of the journal ** df_siwake **.

df_siwake

2. Creating a trial balance

【Thing you want to do】 Create a ** balance sheet ** (T/B: Trial Balance) by summing up the debit amount and credit amount for each account from the journal.

[Coding policy] Trial balance ** df_TB ** is now generated from the journal ** df_siwake ** In the class ** TrialBalance **, define the function ** create_tb ** and the account DataFrame ** df_kamoku **.

In anticipation of finally preparing financial statements, give the following attributes to each account in the trial balance Define ** df_kamoku ** (Note 3).

--Course code: Display order of subjects --Category classification: Whether it belongs to assets, liabilities, or net assets --BS/PL: B/S course or P/L course --Debt classification: Whether it belongs to a debit (asset) or a credit (liability, net asset)

df_kamoku = pd.DataFrame({'Loan items': ['cash', 'accounts receivable', 'Product', 'Accounts payable', 'Capital', 'Retained earnings carried forward', 'Earnings', 'Earnings原価', 'Net income'],
                          'Subject code': ['100', '110', '120', '200', '300', '310', '400', '500', '600'],
                          'Subject classification': ['Assets', 'Assets', 'Assets', 'liabilities', 'liabilities', '純Assets', 'Revenue', 'cost', 'Profit'],
                          'BS/PL': ['BS', 'BS', 'BS', 'BS', 'BS', 'BS', 'PL', 'PL', 'PL'],
                          'Leasing classification': [1, 1, 1, -1, -1, -1, -1, 1, 1]})

And a table that aggregates the amounts of ** df_kamoku ** and the journal ** df_siwake ** for each account.

df = df_siwake.groupby('Loan items').sum()[['Lending amount']]

To combine.

It is easier to handle the debit as a plus and the credit as a minus for the convenience of counting in Pandas (Note 4).

--Credit amount: Debit amount-Credit amount

Create a column with.

class TrialBalance:
    
    df_kamoku = pd.DataFrame({'Loan items': ['cash', 'accounts receivable', 'Product', 'Accounts payable', 'Capital', 'Retained earnings carried forward', 'Earnings', 'Earnings原価', 'Net income'],
                              'Subject code': ['100', '110', '120', '200', '300', '310', '400', '500', '600'],
                              'Subject classification': ['Assets', 'Assets', 'Assets', 'liabilities', 'liabilities', '純Assets', 'Revenue', 'cost', 'Profit'],
                              'BS/PL': ['BS', 'BS', 'BS', 'BS', 'BS', 'BS', 'PL', 'PL', 'PL'],
                              'Leasing classification': [1, 1, 1, -1, -1, -1, -1, 1, 1]})
     
    def create_tb(self, df_siwake):
        df = df_siwake.copy()
        df['Loan items'] = df['Debit subject'] + df['Credit subject'] 
        df['Lending amount'] = df['Debit amount'] - df['Credit amount'] 
        df = df.groupby('Loan items').sum()[['Lending amount']]
        
        df_merge = pd.merge(df, self.df_kamoku, on='Loan items').sort_values('Subject code').reset_index(drop=True)
        
        return df_merge

Instantiate the ** TrialBalance ** class and pass the journal ** df_siwake ** to the function ** create_tb ** Create a trial balance ** df_TB **.

TB = TrialBalance()

df_TB = TB.create_tb(df_siwake)
df_TB

The credit amount for each subject (+ for debit,-for credit) is totaled, and attributes such as subject code and subject classification are added.

All you have to do is divide the trial balance into the balance sheet (B/S) and the income statement (P/L). ** Simply splitting will cause the problem that the debit and credit amounts on the balance sheet do not match **.

For confirmation, only those whose subject classification is "BS" are taken out from the balance trial balance.

df_TB[df_TB['BS/PL']=='BS']

At first glance it looks fine, but this balance sheet (B/S) is Rule ②: The total amount of debits and credits always match Does not meet.

Let's add up the debit amount (the credit amount is +) and the credit amount (the credit amount is-).

df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] > 0)]

df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] < 0)]

print('Total debit amount:',df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] > 0)].sum()['Lending amount'])
print('Total credit amount:',df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] < 0)].sum()['Lending amount'])

Total debit amount: 1300
Total credit amount: -1200

You can see that the total debit amount on the balance sheet (B/S) and the total credit amount do not match.

The difference on this balance sheet (B/S) matches the difference between income and expenses on the income statement (P/L) for the current period, that is, ** net income **.

The procedure for calculating net income from the difference between income and expenses is called ** profit and loss transfer **.

The procedure for transferring ** net income ** on the income statement (P/L) to the ** retained earnings carried forward ** account on the balance sheet (B/S) is called ** capital transfer **. ..

The journal entries for capital transfers are as follows:

df_siwake = siwake.entry(df_siwake, 20200430,
                         [['Net income', 100]],
                         [['Retained earnings carried forward', 100]])

df_siwake[df_siwake['Journal number']==siwake.siwake_no]

Net income is the difference between sales of 300 and cost of sales of 200, which is 100.

Let's take a look at the journal ** df_siwake ** and the trial balance ** df_TB ** after the income statement and capital transfer.

df_siwake

df_TB = TB.create_tb(df_siwake)
df_TB

print('Total debit amount:',df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] > 0)].sum()['Lending amount'])
print('Total credit amount:',df_TB[(df_TB['BS/PL']=='BS') & (df_TB['Lending amount'] < 0)].sum()['Lending amount'])

Total debit amount: 1300
Total credit amount: -1300

By performing profit and loss transfer and capital transfer, we were able to confirm that the debit amount and credit amount on the balance sheet (B/S) match.

3. Preparation of balance sheet (B/S) and income statement (P/L)

【Thing you want to do】 Divide the trial balance into the balance sheet (B/S) and the income statement (P/L).

[Coding policy] Divide the balance sheet ** df_TB ** according to whether the "BS/PL" column is'BS'or'PL'.

By multiplying the "credit amount" by the "credit classification" (+1 and -1), the amount of each debit (+) and credit (-) is returned to the absolute value. Store it in the column labeled "Display Lending".

BS = df_TB[df_TB['BS/PL']=='BS'].reset_index(drop=True)
BS['Show loan'] = BS['Lending amount'] * BS['Leasing classification']
BS[['Subject code','Subject classification', 'Loan items', 'Show loan']]

PL = df_TB[df_TB['BS/PL']=='PL'].reset_index(drop=True)
PL['Show loan'] = PL['Lending amount'] * PL['Leasing classification']
PL[['Subject code','Subject classification', 'Loan items', 'Show loan']]

The balance sheet (B/S) and income statement (P/L) have been created. The image diagram is shown below.

Looking back again,

** 1. Creating journals **

Record the transaction as a ** journal ** in the ** journal **.

** 2. Creating a trial balance (T/B) **

Create a ** balance trial balance ** by aggregating the debit amount and credit amount for each account from the journal.

** 3. Preparation of balance sheet (B/S) and income statement (P/L) **

Divide the trial balance into ** balance sheet (B/S) ** and ** income statement (P/L) ** according to the item.

In the flow of, ** totaling daily transactions and preparing financial statements ** is the procedure for one round of bookkeeping.

Supplement: Error check when creating journals

Define the functions of (2) data type check and (3) balance check in the journal entry class ** Siwake **.

--Check when entering whether the data type of the journal is correct ... ② --Check when entering whether the journals are balanced and borrowed ... ③

Implement as follows.

class Siwake:
    def __init__(self):
        self.siwake_no = 0

    def entry(self, df, date, kari, kashi):             # ...① Supports compound journals
            if self.check_keishiki(date, kari, kashi):  # ...② Data type check
                if self.check_taisyaku(kari, kashi):    # ...③ Balance check
                    self.siwake_no += 1                 # ...Update journal number
            
                    for i in range(len(kari)): #To support compound journals[Debit item, debit amount]Turn the loop as many times as
                    
                        #Convert journal number, date, debit item, debit amount, credit item, credit amount to Sereis and store in DataFrame
                        #The credit subject is"", Credit amount is 0
                        kari_entry = pd.Series([self.siwake_no] + [date] + kari[i] + ["", 0], index=df.columns)          
                        df = df.append(kari_entry, ignore_index=True)
            
                    for i in range(len(kashi)): #To support compound journals[Credit item, credit amount]Turn the loop as many times as
                    
                        #Convert journal number, date, debit item, debit amount, credit item, credit amount to Sereis and store in DataFrame
                        #The debit item is"", The debit amount is 0
                        kashi_entry = pd.Series([self.siwake_no] + [date] + ["", 0] + kashi[i], index=df.columns)
                        df = df.append(kashi_entry, ignore_index=True)
            
                    return df
            
                else:
                    print("Error: Match loan amounts")
                    return df

            else:
                pass

    
    def check_keishiki(self, date, kari, kashi):# ...② Data type check
        for i , k in zip(range(len(kari)),range(len(kashi))):
            if type(date) != int or len(str(date)) != 8:
                print("Error: Enter the date as an 8-digit integer yyyymmdd")
                return False                       
            
            elif len(kari[i]) != 2:
                print("Error: Please enter only the debit item and debit amount")
                return False                
            
            elif type(kari[i][0]) != str or type(kari[i][1]) != int:
                print("Error: Data type should be debit item → character string, debit amount → number")
                return False
            
            elif len(kashi[k]) != 2:
                print("Error: Please enter only the credit item and credit amount")
                return False                
            
            elif type(kashi[k][0]) != str or type(kashi[k][1]) != int:
                print("Error: The data type should be credit item → character string, credit amount → number")
                return False
            
            else:
                return True                    
                                        
    def check_taisyaku(self, kari, kashi):# ...③ Balance check
        kari_sum = 0
        kashi_sum = 0
        
        for i in range(len(kari)):
                kari_sum += kari[i][1]
        
        for i in range(len(kashi)):
                kashi_sum += kashi[i][1]       
        
        if kari_sum != kashi_sum:
            return False
        
        else:
            return True

Create an instance of the Siwake class and create an invalid journal.

siwake = Siwake()

Try entering an invalid value for "Date".

df_siwake = siwake.entry(df_siwake, 1201,
             [['cash', 500]],
             [['Capital', 1000]])

Error: Enter the date as an 8-digit integer yyyymmdd

Let's enter a journal that does not match the loan amount.

df_siwake = siwake.entry(df_siwake, 20200401,
             [['cash', 500]],
             [['Capital', 1000]])

Error: Match loan amounts

I will try to enter a journal with extra items.

df_siwake = siwake.entry(df_siwake, 20200401,
             [['cash', 500, 'Trader A']],
             [['Capital', 1000, 'Trader A']])

Error: Please enter only the debit item and debit amount

Try to enter a journal with an incorrect data type.

df_siwake = siwake.entry(df_siwake, 20200401,
             [['cash', 500]],
             [['Capital', '1000']])

Error: The data type should be credit item → character string, credit amount → number

In practice, check if the input value is empty, check if the account is in the item master, etc. Various check items can be considered, but in this article, only the minimum checks are carried out.

in conclusion

I posted an article on Qiita for the first time.

When I actually output it, it was a very interesting experience, as it came to light one after another that I didn't understand Python or bookkeeping.

From now on

--Evaluation of product inventory and cost of sales --Depreciation of fixed assets --Procedure to carry over to the next term

I would like to see if it can be implemented.