Tenant Ledger to Excel: Checking What a Landlord Claims

To get a scanned tenant ledger into Excel, run the pages through OCR that keeps tables, export the workbook, and turn the amount cells into numbers. Then rebuild the running balance with a formula. Where your balance and the printed balance part ways, you have found either a misread digit or an entry that needs explaining.

The ledger is the landlord's own account of what is owed. In a nonpayment case the amount claimed comes from it, so the first job on either side of the case is to make it add up and to find out what each line is. A spreadsheet does both faster than a calculator and a highlighter, and it leaves a record of how you got there.

Each posted line should be readable before anyone totals it for an unlawful detainer filing. Search the workbook for every charge the landlord entered and every payment credited to the tenant. Match the running balance to the page image before that total goes into the complaint or the answer. One misread fee changes what the notice claims.

What is a tenant ledger and how does it differ from a rent roll?

A tenant ledger, as the term is commonly used, is the transaction history of one tenancy: every charge and every payment in date order, with a balance. A rent roll is a snapshot of a whole property on one date, one line per unit. The ledger answers "what does this tenant owe and why". The rent roll does not.

The same report goes by other names: resident ledger, tenant statement, account history, transaction listing. Go by what the pages contain, not the title. Ask the landlord or opposing counsel what the report is called in their system and what date range it covers.

Three things to establish about any ledger before you convert it:

• Does it start at the beginning? A ledger whose first line is "balance forward" hides everything before that date. Ask for the history back to move-in, or to a zero balance.
• Is it complete to the notice date and to today? You may need both figures.
• Was it printed from a system or kept by hand? A handwritten rent book needs a different approach, covered in the post on a handwritten ledger to Excel.

A lawyer working on a sale or a loan against a building needs the other report, and the post on converting a rent roll to Excel deals with that.

Converting a scanned tenant ledger to a workbook

Convert a scanned tenant ledger by uploading the pages as one file, checking on screen that each transaction sits on its own row, exporting to Excel, and then cleaning the export on a copy. Keep the untouched export. Every later question about a number goes back to it and then to the scan.

1. Put the pages in order. Ledgers print oldest first or newest first. Either is fine, but all pages must run the same way. Check the page numbers printed in the footer, if any.
2. Upload. Schema Law takes a PDF, PNG or JPEG of up to 20 MB. A ledger someone photographed with a phone is accepted, though a flat scan gives recognition a better chance.
3. Compare a page on screen. Count the rows on one scanned page and count the rows in the parsed table for that page. They should be equal.
4. Export. The Excel export puts each table on its own sheet and the remaining text on another. Excel and CSV are on every plan, and the plan decides only how many pages you can convert, as the pricing page shows. The Free plan covers one document a day of up to three pages, which is enough to see how your ledger reads before you pay for anything.
5. Stack the pages. On a new sheet, paste the rows from each page's table one under another. Delete the repeated header rows and any "continued" or page subtotal rows, noting what you removed. The Excel conversion post gives the full routine for joining tables that span several pages.
6. Add a source column. Beside each row, enter the scan's page number. Add an order column numbered 1, 2, 3 down the sheet so you can always restore the printed order after sorting.
7. Turn text into numbers and dates. Exported cells hold characters. Strip currency signs and thousands separators from the money columns and convert them. Convert the date column, and then check that no date moved to a different month, which can happen when day and month are swapped on conversion.

The remaining text sheet matters too. The tenant's name, the unit, the property, the report date and the date range are printed outside the grid and land there. Read them. A ledger for the wrong unit, or one that stops a month early, is a problem you want to find now.

For how recognition decides where rows and columns are, see the explanation of how OCR finds a table on a scanned page.

Charge codes, late fees and credits in tenant ledger columns

Each ledger row is a charge that raises the balance or a credit that lowers it, labelled with a code or short description. Before you analyze anything, build a key that says what every code means and which way it moves the balance. Ask the landlord's side for the code list. Do not guess from the abbreviation.

Start by listing every distinct value in the description or code column. In Excel, copy the column to a new sheet and use Remove Duplicates. Then fill in a key like this one, where the codes are invented examples:

Add a category column to the main sheet and fill it with a lookup against the key. The categories are what let you split the balance by kind: how much of it is rent, how much is fees, and how much is something else.

One amount column or two

Look at how your ledger prints money. It may have separate charge and payment columns, or a single amount column where credits are negative, in parentheses, or followed by CR. Convert either into one signed column for your arithmetic: charges positive, credits negative. After converting, check a few credits by eye. Parentheses and trailing minus signs are small marks that recognition can drop, and a payment that loses its sign becomes a charge.

Rows that need a second look

• Late fees. Sort by category and list each fee with its date and amount. Whether a fee is allowed, and in what amount, is a matter for the lease and your jurisdiction's law. The sheet's job is to isolate them.
• Reversals. A returned payment appears as a payment and then a reversal, perhaps with a fee. Keep all three rows and link them in a note.
• Adjustments. An unexplained ADJ line is a question for the other side. Flag it.
• Third-party payments. Where part of the rent is paid by a program, the ledger may mix the tenant's portion and the program's portion. Tag them separately so you can total the tenant's share alone.
• Duplicate charges. Sort by category and date and look for the same charge twice in one month.

Does the running balance on a tenant ledger survive conversion?

The balance column survives as characters, like every other column, and it is your best check on the rest. Recalculate the balance from the amounts with a formula and compare your figure with the printed one on every row. Where the two first differ, a digit was misread on that row or the ledger's own arithmetic has a gap.

Set up the check

With the rows in printed order, oldest first, and columns for signed amount (D) and printed balance (E):

F2: =E2                      first row: take the printed balance as given
F3: =F2+D3                   your recalculated balance, filled down
G3: =ROUND(F3-E3,2)          difference, filled down

If the ledger starts with a "balance forward" row, that figure is your starting point, and it is unverified until you see the earlier history.

Filter column G for anything that is not zero and work from the top. The first nonzero row is where to look.

Reading the difference

The last case is the one that matters to the case. When every cell on a row matches the scan and the balance still jumps, the landlord's own report contains an unexplained change. Note the row, the page and the amount, and ask for an explanation.

The types of misread to expect in figures, such as 1 and 7 or 5 and 6, are catalogued in the post on why OCR gets numbers wrong.

Ledgers printed newest first

If the ledger runs newest to oldest, sort by your order column in reverse before filling the formula down. Use the order column, not the date column, because several entries can share a date and their sequence affects the balance on each line.

Matching ledger entries to rent receipts and money order stubs

Match each payment the tenant can prove to a credit row on the ledger by amount and date, and list what is left over on both sides. A proven payment with no ledger row is the item to raise first. A receipt for a different amount needs a question.

Gather the tenant's proof

The tenant's proof can take any form: carbon receipts from a rent book, money order stubs, cashier's check copies, bank statements, payment app screenshots, confirmation emails from a rent portal. Photograph or scan each one flat. Run them through OCR so the folder is searchable, but type the proof list yourself:

A money order stub is a small slip, and its print can be faint. A handwritten receipt is handwriting. OCR is weaker on faint print and handwriting than on a clean system-printed ledger, so use the converted text to find a stub and your own eyes to read it.

Match

1. Add a Proof column to the ledger sheet.
2. For each proof, find credit rows with the same amount within a few days after the proof's date. The ledger may show the day the office entered a payment, which need not be the day it was paid.
3. Enter the proof number on the matching row. One proof matches one row.
4. Where a tenant paid one month in two parts, two proofs may match one credit row or two. Note what you did.

Then make three lists:

• Proof with no ledger row. Payments the tenant can show that the landlord has not credited.
• Ledger credits with no proof. No dispute needed, though they tell you which records the tenant is missing.
• Mismatches. Same date, different amount. Look at the scan of both before drawing any conclusion.

A money order stub shows that a money order was bought. Whether it shows the landlord received it may take more, such as a copy of the cashed item from the issuer. Raise that with the lawyer early, so there is time to ask the issuer for its records.

How do you show which month a rent payment was applied to?

Show it with an allocation schedule: list each month's rent as its own line, then walk through the payments in date order and record which charges each one paid down. The rule for applying a payment comes from the lease, any instruction the tenant gave, and your jurisdiction's law. The spreadsheet only carries out the rule you choose.

A ledger's running balance hides this question. It shows a total owed, not which months are unpaid. That matters when a notice demands rent for named months, when fees and rent are mixed in one balance, or when a limitation on what a notice may demand applies in your jurisdiction.

A worked example

Rent is 1,000 a month. The fictional tenant, Mr. Okonkwo, paid in full in January, paid 600 in February, was charged a 50 late fee, and paid 1,000 in March. The ledger shows a balance of 450 after the March payment and 1,450 after April's rent posts.

Under one possible rule, each payment goes to the oldest unpaid charge of any kind:

Under another, each payment goes to the rent for the month in which it was paid, and any surplus goes to the oldest unpaid item:

The balance is 450 either way. The description of the debt is not the same: 450 of March rent in the first schedule, 400 of February rent plus a 50 fee in the second. Which description is right is a legal question. Build both when the answer is contested, and label each with the rule it assumes.

Building the schedule in Excel

1. On a new sheet, list every charge as a row: date, category, amount, and an Unpaid column that starts equal to the amount.
2. List the payments in date order on a second sheet.
3. For each payment, reduce the Unpaid cells of the charges it is applied to, in the order your rule gives, and write one allocation row per charge touched: payment date, charge date, category, amount applied.
4. Check that the allocation rows for each payment add up to the payment, and that the sum of the Unpaid column equals the ledger's final balance.
5. Total Unpaid by category. That gives rent owed, fees owed and other charges owed as separate figures.

Do the allocation by hand for a tenancy of a year or two. It is slower than a formula, and every allocation row can be explained on its own, with the ledger page beside it.

Keep the scan, the untouched export, the cleaned sheet and the schedules together, and cite the ledger page for every figure you present. The post on landlord tenant document OCR covers the notice, the lease and the service pages that this balance has to be read against.

A ledger names a person and shows their payment history. Decide under your own duties whether it may go to an online tool, and read the commitments Schema Law states for every plan as written. The site claims no certification or compliance standard.