Feb 1, 2018

How do I create a Bank Reconciliation Statement?

1 comment

Edited: Apr 3, 2018


UPDATE: A Bank Reconciliation Template is now available on the "Free Downloads" page of this website. Try it out!


Ledger1 doesn’t come with a built-in selection of canned reports. We hope to include downloadable sample reports in the future. Currently, the way to create reports is to start with the reports page and use the formulas to design the report you need. Here is one website that provides a large selection of free sample reports: https://www.sampletemplates.com/?s=bank+reconciliation


Here is one way to create an automated Bank Reconciliation Statement that would allow you to enter a date and account name and then complete the report by adding reconciling items to the body of the report:


1. Use the “cleared” column on the Ledger page. When you enter a transaction, or import a group of transactions, Ledger1 marks them as “cleared” by default, by entering a “C” in the cleared column. If you remove the “C” for pending items, Ledger1 will display 2 balances in the upper left of the page: an actual “cleared” balance and a balance that includes all pending items. These are the two numbers you are reconciling. You can add these two numbers to any report using the following two formulas:


To provide a cleared balance:

=SUMIFS(Transaction_Table[Credit],Transaction_Table[Account],C3,Transaction_Table[[Date ]],"<="&D2,Transaction_Table[CL],"C")-SUMIFS(Transaction_Table[Debit],Transaction_Table[Account],C3,Transaction_Table[[Date ]],"<="&D2,Transaction_Table[CL],"C")


In this example, the Account Name is assumed to be in cell C3, and the reconciliation date is assumed to be in cell D2.


To provide a pending balance:

=SUMIFS(Transaction_Table[Credit],Transaction_Table[Account],$C$3,Transaction_Table[[Date ]],"<="&$D$2,Transaction_Table[CL],"C")-SUMIFS(Transaction_Table[Debit],Transaction_Table[Account],$C$3,Transaction_Table[[Date ]],"<="&$D$2,Transaction_Table[CL],"C")


If you use this method, all you’ll need to do is enter and account name and date in the referenced cells and the report will be updated.


Then you can add more lines to the report as needed. If you have a large number of pending items that you need to list on your report, you could filter the Transaction Data Table to show only the pending items, then copy and paste them to your report. The more advanced way would be to create formulas to lookup the pending items, or even use a pivot table in the body of the report.


Apr 3, 2018

UPDATE: A Bank Reconciliation spreadsheet is now available on the "Free Downloads" page. Just download it and add it to your existing Ledger1. Try it out!

New Posts
  • Ledger1
    Dec 6, 2018

    If you are using the latest version of Excel, as of November 2018, a new stock price updating method is available. Here is a great article from Excel University on how to use it: https://www.excel-university.com/stock-data-type-stock-quotes-and-more/
  • Ledger1
    Dec 6, 2018

    The Securities Page in Ledger1 uses services provided by EIX Trading to import live stock prices. Price (delayed 15 minutes) is one of many stock statistics that EIX makes available for free. For example, here are some other widely used stats that are easily imported to Ledger1: Opening price (open) High for the day (high) Low for the day (low) Closing price (close) Volume (latestVolume) Average Volume (avgTotalVolume) 52 week high (week52High) 52 week low (week52Low) The formula Ledger1 uses to bring live data into column F on the Securities Page is this: =IF((OR([@[Security Type]]="Stock",[@[Security Type]]="ETF"))=FALSE,0, NUMBERVALUE(WEBSERVICE("https://api.iextrading.com/1.0/stock/" & D8 & "/quote/latestPrice"))) The last term in the formula is "lastestPrice". Just replace this code with any of the codes shown in parentheses above to get the statistic you want and you'll be on your way to creating a powerful trading tool. Many more codes are available at the the following EIX Trading website: https://iextrading.com/developer/docs/#quote
  • Ledger1
    Jan 28, 2018

    Ledger1 can be set up to export tax data to other applications via the .TXF file format. Programs such as H&R Block and TurboTax allow you to import data via a standard file format called “Tax Exchange Format”. To see how easy it is to set this feature up, view this tutorial: https://www.ledger1.com/tax-data-export-tutorial