detailtrialbalance

MEDITECH Detail Trial Balance Parser

A Streamlit application for parsing MEDITECH Detail Trial Balance PDFs into structured Excel workbooks.

The app allows a user to upload a MEDITECH Detail Trial Balance PDF, extracts transaction-level detail and account summary data, previews the parsed results in the browser, and exports the results to an Excel workbook.

---

Features

• Upload a MEDITECH Detail Trial Balance PDF

• Parse transaction detail into a structured table

• Parse account-level summary balances

• Preview parsed data inside Streamlit

• Export results to an Excel workbook

• Creates separate Excel tabs for:

  • Transactions
  • Account Summary

• Adds basic workbook formatting:

  • Report title
  • Frozen panes
  • Excel tables
  • Auto-sized columns

---

Project Structure

detailtrialbalance/
│
├── streamlit_app.py           # Streamlit user interface
├── trial_balance_parser.py    # PDF parsing and Excel export logic
├── requirements.txt           # Python package dependencies
├── README.md                  # Project documentation
└── .gitignore                 # Files excluded from Git

---

Requirements

This project uses Python 3.10+.

Required Python packages:

streamlit
pdfplumber
pandas
openpyxl

---

Setup

1. Clone the repository

git clone <your-repository-url>
cd detailtrialbalance

2. Create a virtual environment

python -m venv .venv

3. Activate the virtual environment

.\.venv\Scripts\activate

4. Install dependencies

pip install -r requirements.txt

---

Running the Application

From the project folder, run:

streamlit run streamlit_app.py

Streamlit will open the application in your browser.

If it does not open automatically, copy the local URL from the terminal and paste it into your browser.

---

How to Use

1. Open the Streamlit app.
2. Click Browse files.
3. Upload a MEDITECH Detail Trial Balance PDF.
4. Click Parse PDF.
5. Review the parsed transaction and account summary previews.
6. Click Download Excel File to save the parsed workbook.

---

Output Workbook

The generated Excel workbook contains two sheets.

Transactions

This sheet includes transaction-level detail such as:

• Account Name
• Account Number
• Journal
• Date
• Batch
• Entry
• Debits
• Credits
• Description

Account Summary

This sheet includes account-level totals such as:

• Account Name
• Account Number
• Opening Balance
• Total Debits
• Total Credits
• Net Change
• Closing Balance

---

Main Files

streamlit_app.py

Handles the web interface.

Responsibilities:

• Displays the upload screen
• Accepts PDF uploads
• Calls the parser module
• Displays parsed results
• Provides the Excel download button

trial_balance_parser.py

Contains the reusable parsing and export logic.

Responsibilities:

• Reads the uploaded PDF
• Extracts text using pdfplumber
• Parses account headers
• Parses transaction lines
• Parses account summaries
• Cleans repeated page header text from descriptions
• Builds the Excel file in memory

---

Command-Line Usage

The parser module can also be used outside of Streamlit.

Example:

from trial_balance_parser import parse_trial_balance_to_excel

pdf_path = r"C:\Users\YourName\Downloads\trial_balance.pdf"
out_xlsx = "parsed_trial_balance.xlsx"

excel_bytes, df_txn, df_acct, title = parse_trial_balance_to_excel(pdf_path)

with open(out_xlsx, "wb") as f:
    f.write(excel_bytes)

print("Parsed successfully")
print("Transactions:", df_txn.shape)
print("Account Summary:", df_acct.shape)
print("Output:", out_xlsx)

---

Git Ignore Recommendations

Do not commit PDFs, Excel output files, virtual environments, or sensitive local configuration files.

Recommended .gitignore:

.venv/
__pycache__/
*.pyc
.env

# Input/output files
*.pdf
*.xlsx
*.xls

# Streamlit local config
.streamlit/secrets.toml

---

Important Data Handling Note

Detail Trial Balance reports may contain sensitive financial information.

Do not commit source PDFs or exported Excel files to the repository.

Only commit source code and documentation.

---

Troubleshooting

git is not recognized

If PowerShell says:

git : The term 'git' is not recognized

Git is either not installed or not added to the Windows PATH.

Install Git for Windows and choose the option:

Git from the command line and also from 3rd-party software

Then restart PyCharm and confirm:

git --version

---

Streamlit will not start

Make sure the virtual environment is activated:

.\.venv\Scripts\activate

Then run:

streamlit run streamlit_app.py

---

Missing packages

If you see an import error such as:

ModuleNotFoundError: No module named 'pdfplumber'

Run:

pip install -r requirements.txt

---

Excel export error

If Excel export fails, confirm that the parser returned valid dataframes.

The Streamlit app should display:

• Number of transaction rows
• Number of account summary rows

If both are zero, the PDF text layout may not match the parser’s expected MEDITECH report format.

---

PDF parses zero rows

This parser depends on text extraction from pdfplumber.

A PDF may fail to parse correctly if:

• It is scanned as an image instead of text
• The report format is different from the expected MEDITECH Detail Trial Balance layout
• The account number pattern is different
• Journal/date/entry fields appear in a different order
• The PDF text columns are not extracted cleanly

The current parser expects account headers similar to:

00.00000.00000 - 00 Account Name

And transaction lines similar to:

JOURNAL 01/01/26 12345 debit credit description

---

Development Notes

The original parser was written as a standalone script using a hard-coded PDF path and output file path.

The current version separates the logic into a reusable module:

trial_balance_parser.py

This allows the same parsing logic to be used by:

• Streamlit
• A command-line script
• Future scheduled jobs
• Future internal web applications

---

Updating Dependencies

To install dependencies:

pip install -r requirements.txt

To add a new dependency, install it first:

pip install package-name

Then update requirements.txt manually or regenerate it.

Manual minimal approach:

streamlit
pdfplumber
pandas
openpyxl

Full freeze approach:

pip freeze > requirements.txt

The manual approach is usually cleaner for this small application.

---

Pushing Updates to the Repository

After making changes:

git status
git add .
git commit -m "Update trial balance parser application"
git push

Before pushing, confirm that no PDFs, Excel exports, .env files, or virtual environment folders are being committed.

---

Future Enhancements

Potential improvements:

• Add validation checks comparing transaction totals to account summary totals
• Add better error handling for unsupported PDF layouts
• Add support for multiple PDF uploads
• Add a reconciliation summary tab
• Add logging
• Add user authentication if deployed internally
• Add configurable output file names
• Add support for additional MEDITECH financial reports

---

Application Summary

This application converts MEDITECH Detail Trial Balance PDFs into structured Excel output with minimal manual cleanup. It is designed to support finance, reporting, and data analysis workflows where PDF-based reports need to be transformed into spreadsheet-ready datasets.