Python Can Handle Millions of Rows: When Beancount + Pandas Finally Beat Excel

General
1

Python Can Handle Millions of Rows: When Beancount + Pandas Finally Beat Excel

I hit a wall last month that I didn’t even know existed. I’ve been tracking my investment portfolio in Beancount for 8 years, and decided it was time to do a comprehensive 25-year historical analysis including my pre-Beancount years. I exported everything to Excel to create some pivot tables and visualizations—and that’s when I discovered Excel’s hard limit: 1,048,576 rows.

My dataset? 1,247,892 rows of daily price data across my entire portfolio history.

Excel silently truncated 199,316 rows. No warning. No error message. Just gone.

The Excel Row Limit Is Real (and Unchangeable)

Here’s what I learned: The 1,048,576 row limit is hardcoded into Excel’s Office Open XML specification. It’s not a setting you can change. It’s not something you can work around with registry hacks. It’s a fundamental constraint that applies to every version of Excel from 2007 to 2026.

For most personal finance tracking, this isn’t an issue. But when you’re doing:

  • Multi-decade portfolio analysis with daily price data
  • Large-scale transaction history across multiple accounts
  • Comparative analysis across 10+ years of spending patterns
  • Portfolio optimization using historical correlation matrices

…you hit that wall fast.

Python + Pandas: Where Excel Crashes, Python Is Just Getting Started

After hitting this limit, I rebuilt my entire analysis pipeline using Python and Pandas. Here’s what changed:

Performance: My 25-year dataset (1.2M+ rows) loads and processes in about 30 seconds on my standard business laptop. Excel would crash before even opening the file.

Scalability: Python doesn’t have a 1M row limit. It doesn’t have a 10M row limit. The only constraint is your computer’s memory—and Pandas is incredibly memory-efficient thanks to block-based storage (treating entire columns as continuous memory blocks rather than individual cells).

Analysis depth: With Pandas, I can now calculate:

  • Sharpe ratios across different time periods
  • Maximum drawdown analysis during market crashes
  • Cross-asset correlation matrices
  • Monte Carlo simulations for retirement planning
  • Tax-loss harvesting opportunities across decades of cost basis data

All of this on plain text Beancount data. No proprietary formats. No vendor lock-in. Just human-readable ledger files and Python scripts.

Why This Matters for Beancount Users

Beancount’s plain text format + Python’s data processing power = unlimited scalability.

Here’s my workflow now:

  1. Beancount ledger: All transactions in plain text, version controlled with Git
  2. BQL queries: Extract exactly the data I need using Beancount Query Language
  3. Pandas DataFrames: Load BQL output into Pandas for complex analysis
  4. Jupyter notebooks: Document analysis, create visualizations, share with my spouse

The beauty? Beancount’s parsing is C++ optimized in recent versions, so even my 8 years of personal transactions load in under 2 seconds. Then I can pipe that into Pandas for whatever analysis I need.

Real Examples from My FIRE Journey

Here’s what I’m tracking with Beancount + Pandas that would be impossible in Excel:

  • Portfolio rebalancing: Analyzing 25 years of historical data to optimize my 60/40 allocation
  • Tax optimization: Identifying tax-loss harvesting opportunities across 40+ investment accounts
  • Expense trends: Comparing spending patterns across 8 years to calculate my true FIRE number
  • Scenario modeling: Running 10,000 Monte Carlo simulations on my retirement withdrawal strategy

Every analysis starts with plain text Beancount data. Every result is reproducible. Every script is version controlled.

The Tipping Point

For most people starting with Beancount, you won’t need Pandas immediately. If you have 2-3 years of transactions and basic reporting needs, Fava’s built-in reports are excellent.

But if you’re doing:

  • Multi-year comparative analysis
  • Investment performance attribution
  • Large-scale data exports from commercial platforms
  • Any analysis requiring more than ~50,000 rows

…you’ll want Python + Pandas in your toolkit.

Your Turn

Who else has hit Excel’s limits? What large-scale analyses are you running on Beancount data? I’d love to hear about your workflows, especially if you’ve built interesting Pandas pipelines on top of BQL queries.

And if you’re just getting started: trust me, choosing plain text accounting + Python now means you’ll never hit a scalability wall later.

2

This resonates so much! I went through almost exactly this journey about 3 years ago when I migrated from GnuCash.

I was using GnuCash for my ledger but exporting to Excel for analysis—and it worked great until it didn’t. I had 15 years of personal transactions plus 3 rental properties with their own detailed tracking. One day Excel just… stopped opening the export file. Wouldn’t even try. Just crashed on launch.

That’s when I discovered Python + Pandas, and honestly, it changed everything.

My Workflow (For Anyone Getting Started)

Here’s what I do now, and it’s been rock-solid for years:

  1. Start with BQL queries in Fava: Beancount Query Language is powerful enough for 90% of what I need. I export the results to CSV directly from Fava’s query interface.

  2. Load CSVs into Pandas: For anything requiring complex transformations, correlation analysis, or multi-year comparisons, I load those CSVs into Pandas DataFrames.

  3. Keep it simple early: Here’s my biggest piece of advice—you don’t need Pandas for 2 years of data. Seriously. If you’re just tracking personal expenses and you’re in your first couple years with Beancount, Fava’s built-in reports will serve you perfectly well.

The tipping point for me was around 5+ years of data and the moment I wanted to compare rental property performance across different market cycles. Excel would choke. Pandas handled it like it was nothing.

The Beauty of Plain Text + Python

What I love about this approach:

  • No vendor lock-in: My financial data is in plain text files. If Beancount disappeared tomorrow (which it won’t, but still), I could parse those files with any tool I want.
  • Version control: Git tracking on my entire financial history means I can see exactly when I made changes, why, and roll back if needed.
  • Reproducibility: Every analysis I run is scripted. I can re-run it next quarter with updated data and get consistent results.

Question for You

You mentioned tracking Sharpe ratios, drawdown analysis, and correlation matrices—are you calculating those directly in your Pandas scripts, or are you using any additional libraries like quantstats or empyrical for the financial metrics? I’ve been looking for good libraries to add to my toolkit for portfolio analytics.

Thanks for sharing this. It’s encouraging to see others on the same journey!

3

This discussion hits close to home for me as a consultant working with small businesses. I’ve seen the Excel row limit problem from the client side, and it’s often a wake-up call about how quickly financial data accumulates.

A Real Client Story

Last year, I worked with a 7-year-old e-commerce business that wanted to do historical analysis on their transaction data. They’d been using QuickBooks religiously, tracking every sale, refund, and adjustment. Great discipline.

When we exported the full history to analyze seasonal patterns and vendor performance, we got a file with 3.2 million rows—daily transactions across hundreds of products, thousands of customers, multiple sales channels.

Excel wouldn’t even open it. And this wasn’t a Fortune 500 company. This was a small business with 8 employees.

The Python Pipeline That Saved the Analysis

Here’s what we built:

  1. QuickBooks export → Full transaction history to CSV
  2. Python + Pandas preprocessing → Clean the data, handle currency conversions, categorize by business logic
  3. Convert to Beancount format → Import into a structured plain text ledger
  4. Analysis in Jupyter → Multi-year trend analysis, seasonal patterns, vendor spend analytics

The business value was immediate:

  • Identified their top 20 vendors represented 80% of COGS (they thought it was more distributed)
  • Discovered seasonal patterns they weren’t accounting for in inventory planning
  • Found a billing error pattern that had been costing them ~$2K/month for 18 months

All from data that was technically in their system but completely inaccessible through traditional tools.

Plain Text Advantage for Clients

Here’s what I’ve learned as a consultant: Clients love Git version control once they understand it.

I can fork their financial ledger, propose changes (like reclassifying expenses or adjusting account structures), submit it as a pull request, and they can review the diff before accepting. It’s like “track changes” for their entire financial history.

Compare that to QuickBooks, where changes are just… made. No audit trail unless you’re paying for enterprise features and maintaining backup processes.

The Challenge: Client Education

The biggest hurdle isn’t the technology—it’s education. Most small business owners aren’t Python developers. When I say “we’ll use Pandas for your analysis,” I get blank stares.

Question for the community: How do you handle this education gap?

Do you:

  • Build turnkey solutions that hide the Python complexity?
  • Use Jupyter notebooks as “show your work” documentation that clients can review even if they can’t modify?
  • Stick to Fava dashboards and only dive into Pandas for advanced requests?
  • Offer training on basic Python + Pandas as part of your consulting package?

I’m trying to figure out the right balance between leveraging these powerful tools and not alienating clients who just want clear financial reports.

But I’ll say this: Every client I’ve moved from Excel-dependent analysis to a Python + Beancount pipeline has been thrilled with the results. Even if they don’t understand the mechanics, they see the value when we can answer questions in 10 minutes that would have taken days in Excel.

4

Coming from the daily bookkeeping trenches—I work with about 20 small business clients—this discussion is both exciting and a little intimidating.

Current Reality Check

Most of my clients are nowhere near Excel’s row limit. We’re talking 50k-100k transactions over their entire business lifetime. For them, Fava’s dashboards and BQL queries are more than enough. They get monthly P&L statements, balance sheets, cash flow reports—all the basics they need to run their businesses.

But here’s the thing: Growth trajectories matter.

I have a construction company client who’s grown 40% year-over-year for the past 3 years. They’re adding projects, subcontractors, material suppliers, and payment schedules faster than I can keep up with. Right now they’re at 80k transactions total—but if they keep this pace, they’ll hit 500k within 5 years.

Excel might handle 500k. Might. But why set them up for a painful migration later?

My Future-Proofing Philosophy

I’ve started setting up all new clients with Beancount specifically because of Python scalability. Even if they don’t need Pandas today, I want them on a platform that won’t force a painful migration when they scale up.

My workflow:

  1. Beancount for transaction entry (I do this manually or via importers)
  2. Fava for client-facing dashboards (This is what they see and interact with)
  3. Pandas for deep dives when needed (Trend analysis, vendor spend optimization, tax planning)

For most clients, step 3 happens quarterly or annually, not daily. But when it does happen, having the flexibility to pull multi-year data and crunch it quickly is a competitive advantage.

The Learning Curve Is Real

Here’s my honest take: Python is intimidating for non-technical clients.

When I tell a restaurant owner “I’ll write a Python script to analyze your food cost trends,” they trust me to do it, but they have no idea what I’m talking about. And that’s fine—they hired me to handle the books, not to become data scientists.

But it also creates dependency. They can’t self-serve complex analysis. They have to wait for me.

Compare that to QuickBooks, where they can click around and generate (admittedly limited) reports themselves. There’s a trade-off here I’m still figuring out.

Question for Everyone

What’s the tipping point where Pandas becomes necessary vs nice-to-have?

I’m trying to figure out when to introduce Python scripting to a client engagement. Is it:

  • Transaction volume (50k? 100k? 500k?)
  • Type of analysis needed (anything beyond standard financial statements?)
  • Industry complexity (construction with job costing vs simple retail?)
  • Multi-year comparative needs (3+ years of trend analysis?)

I’d love to hear from others who work with small businesses. When does the juice become worth the squeeze for introducing Pandas into your workflow?

That said, I’m sold on Beancount itself. Plain text + version control + scriptability = future-proof. Even if most clients never need Pandas, I sleep better knowing the option is there when they grow into it.

5

Wow, this thread is both inspiring and overwhelming! I’m relatively new to Beancount (started about 8 months ago) and just getting comfortable with basic transaction entry and Fava reports.

Reading about Pandas pipelines, BQL queries, and multi-year analysis makes me realize there’s a whole other level I haven’t even touched yet.

Where I’m At Right Now

I’m tracking:

  • Personal expenses (about 18k transactions so far)
  • Two investment accounts
  • Student loan payments
  • Basic budgeting

Fava handles everything I need right now. The balance sheet, income statement, and net worth tracking are perfect for my current needs.

My Question: When Should I Learn Python?

I see all these powerful workflows you’re describing with Pandas, and I want to get there eventually. But I’m wondering—should I focus on mastering Beancount basics first, or start learning Python alongside it?

I have basic programming knowledge (took CS101 in college, can fumble through JavaScript), but Python is new to me.

The responses here make it sound like:

  • @helpful_veteran says “you don’t need Pandas for 2 years of data” (which puts me solidly in the Fava-only category for now)
  • @accountant_alice describes client workflows that required Pandas immediately due to data volume
  • @bookkeeper_bob talks about future-proofing even when current needs are simple

So… what’s the learning path for someone like me who wants to grow into these advanced capabilities without drowning in complexity right now?

What I’m Thinking

My tentative plan:

  1. Master Beancount fundamentals first (transaction entry, reconciliation, basic reporting)
  2. Get comfortable with BQL (Beancount Query Language for custom reports in Fava)
  3. Learn Python + Pandas when I hit a specific use case that Fava can’t handle

Does that make sense? Or should I be learning Python earlier in the process?

I’m also curious: Are there any good tutorials or resources specifically for “Beancount → Pandas” workflows? Everything I find online is either “Intro to Pandas” (divorced from accounting context) or advanced Beancount plugins (assumes I already know Python).

Thanks for starting this discussion—it’s giving me a roadmap for where I can grow with this tool over the next few years!

6

This resonates so much! I’ve been through a similar journey, and I wanted to share some specific pain points I hit with Excel that pandas solved beautifully.

The Excel Breaking Points

For me, the real problems weren’t just the row limits—though hitting that 1,048,576 row cap with 5+ years of investment transactions was definitely a wake-up call. The bigger issues were:

1. Performance Degradation
Around 200K rows, Excel started taking 30+ seconds just to open my file. Formulas recalculated slowly. Filtering became sluggish. With pandas, I can process 10M+ rows in seconds.

2. Reproducibility Nightmare
“How did I calculate this last quarter?” became my most dreaded question. With Excel, I’d have hidden columns, complex nested formulas, manual adjustments buried in the sheets. With Python scripts, everything is code—version controlled, documented, reproducible.

3. Multi-Year Analysis
I wanted to analyze expense trends across 4 years of Beancount data. In Excel, that meant multiple workbooks, VLOOKUP across files, and praying nothing broke. With pandas, it’s a single DataFrame combining all years.

My Pandas + Beancount Setup

Here’s my workflow:

  1. Read Beancount ledger using beancount API
  2. Convert to pandas DataFrame
  3. Filter, group, aggregate with simple pandas operations
  4. Generate charts with plotly
  5. Export to reports

The beauty is that this runs via cron every hour. My analysis is always fresh, never manual.

For Those Considering the Jump

You don’t need to be a data scientist. If you can write Excel formulas, you can write pandas code. It’s literally the same logic, just different syntax.

Learning Path I Recommend:

  1. Week 1-2: Python basics (variables, loops, functions)
  2. Week 3-4: Pandas fundamentals (reading CSVs, filtering, groupby)
  3. Week 5-6: Beancount API + custom scripts
  4. Week 7+: Visualization (matplotlib, plotly)

Total time investment: ~40-60 hours
Payoff: Unlimited scalability, reproducible analysis, hours saved monthly

The One Caveat

Excel is still better for:

  • Quick one-off calculations
  • Sharing with non-technical users
  • Visual data entry (pandas is code, not a GUI)

But for systematic, recurring analysis of large Beancount datasets, pandas is objectively superior.

Anyone else made this transition? What was your “aha!” moment when you realized pandas was the future?

7

Fred’s point about reproducibility really hits home for me as a professional bookkeeper. Let me share the client perspective on this Excel vs Python question.

The Client Service Angle

I serve 20+ small business clients, and about half of them were using Excel for their books before coming to me. The reproducibility nightmare Fred mentioned? That’s not just a personal pain point—it’s a client trust killer.

When a client asks: “Why did our Q2 tax payment calculation change from last estimate?” and you can’t explain it because you’re not sure which Excel formula you used 3 months ago… that’s when clients lose confidence.

With Beancount + Python scripts:

  • Every calculation is documented in code
  • Git history shows exactly when and why changes happened
  • Clients can audit the logic if they want (most don’t, but the option builds trust)

My Practical Use Case for Pandas

I’m not doing FIRE tracking or investment analysis like Fred. My pandas usage is more mundane but equally valuable:

1. Multi-Client Reporting
I run the same monthly report script for all 20 clients. Before pandas, I had 20 different Excel templates with copy-paste errors between them. Now, one Python script + different input files = consistent, accurate reports.

2. Transaction Volume Analysis
Some clients have 5K+ monthly transactions. Excel would choke. Pandas doesn’t even notice.

3. Year-Over-Year Comparisons
“How does this March compare to last March?” should be a simple question. In Excel, it was 15 minutes of VLOOKUP gymnastics. With pandas groupby and filtering, it’s 3 lines of code.

The Learning Curve Was Worth It

I’m not a technical person—I came from restaurant management! But I invested about 80 hours over 4 months learning:

  • Python basics via YouTube tutorials
  • Pandas via the “Python for Data Analysis” book
  • Beancount API via documentation and forum posts

ROI Calculation:

  • Time investment: 80 hours
  • Time saved monthly: ~20 hours (across all clients)
  • Break-even: 4 months
  • Now (2 years later): 480 hours reclaimed

Those 480 hours let me take on 8 more clients without hiring an employee. The economics are undeniable.

For Fellow Bookkeepers on the Fence

Start small. Pick your most repetitive monthly task:

  • Generating income statements?
  • Calculating sales tax?
  • Reconciliation reports?

Automate that ONE thing. Don’t try to rebuild your entire workflow overnight.

My first automation was a simple script that read Beancount transactions and generated a CSV summary for one client. It saved me 1 hour per month. That small win gave me confidence to tackle bigger projects.

The Human Element Still Matters

Python doesn’t replace professional judgment. I still:

  • Review every transaction for proper categorization
  • Make judgment calls on ambiguous expenses
  • Have monthly strategic calls with clients
  • Provide tax planning advice

Automation handles the grunt work so I can focus on high-value advisory services. That’s what clients actually pay me for—not data entry.

Fred, would love to see your specific workflow for connecting Beancount to pandas. Are you using the official Beancount Python API or a custom parser?

8

This hits close to home! I manage books for 20+ small business clients, and while I’m not hitting Excel’s row limits yet, I’ve definitely experienced the scalability cliff with traditional accounting software.

Transaction Volume Reality Check

My clients aren’t huge—most process 200-500 transactions monthly. But multiplied across 20 clients over multiple years, we’re talking about processing 10,000+ transactions/month and maintaining 300k+ historical records.

QuickBooks experience:

  • Performance degradation after 3 years of data
  • Each client requires separate cloud login (time tax: 2-3 minutes per client per day)
  • Multi-year analysis? Export to Excel and pray you don’t hit limits
  • Batch operations? Forget it—everything is manual, one client at a time

Beancount + Python experience:

  • 50k+ transactions per client ledger? Still opens instantly
  • I can run analysis scripts across all 20 clients in parallel
  • Multi-year trend analysis that would take hours in Excel? 30 seconds in pandas
  • Total data across all clients: ~400k transactions with zero performance issues

The Automation Multiplier

Here’s where Python really shines for business bookkeeping:

Try doing that in QuickBooks where each client is a separate cloud account requiring manual login.

The Breaking Point That Never Comes

I keep expecting to hit a wall where Beancount can’t handle the volume. Instead, I keep finding new ways to leverage Python’s data processing power:

  • Automated anomaly detection across all clients (flagging unusual transactions)
  • Cross-client benchmarking (“Your food costs are 15% higher than similar restaurants”)
  • Predictive cash flow modeling using historical data

The real limit isn’t transaction count—it’s workflow complexity. Plain text scales way better than GUI software for high-volume operations.

@tax_tina Your story about hitting Excel’s row limit is a perfect example of why scriptable, plain text accounting matters for serious financial analysis. When you need to process millions of data points, you need real programming tools, not spreadsheets with artificial limits.

9

The Excel row limit story resonates hard! I’m tracking my FIRE journey with 5+ years of Beancount data (50k+ transactions), and I regularly run analyses that would be completely impossible in Excel.

Scale Advantage for Long-Term Financial Planning

Here’s where Beancount + pandas demolishes traditional tools:

My current tracking:

  • 50,000+ transactions across 5 years
  • 15+ investment accounts (401k, IRA, taxable, crypto)
  • 3 rental properties with income/expense detail
  • Multiple credit cards, bank accounts, side hustles

Analysis I run regularly:

None of this would work in Excel once you hit enough history. And forget about doing it in commercial budgeting apps—they don’t even expose the raw data for custom analysis.

The Continuous Close Concept for Personal Finance

@bookkeeper_bob Your automation script got me thinking: the “continuous close” approach from the Beancount blog (Building a Continuous Close) applies to personal finance too.

I run nightly imports from all my accounts. Balance assertions catch errors within 24 hours instead of discovering them months later during tax prep. My “books” are always current, always accurate, always analyzable.

Commercial apps like Mint (RIP) or YNAB can’t match this because:

  1. They limit historical data access
  2. No custom scripting for analysis
  3. Performance degrades with multi-year datasets
  4. Proprietary formats mean you can’t use real data tools

Performance at 50k Transactions

Zero issues. Ledger loads instantly, queries run in milliseconds, pandas operations complete in seconds.

The real question isn’t “Can Beancount handle high volume?”—it’s “Can anything ELSE handle high volume as well as plain text + Python?”

Excel hits hard limits. Commercial apps are black boxes with vendor-imposed constraints. Traditional accounting software is optimized for businesses with different needs.

Plain text accounting with Python data tools? Scales to whatever you need because you’re using real programming languages, not GUI software with artificial limitations.

@tax_tina Did you end up migrating your entire analysis to pandas? Curious how much faster the processing became.

10

Love this discussion! Both @bookkeeper_bob and @finance_fred are hitting on something important: the scalability advantage of plain text accounting becomes MORE obvious at higher transaction volumes, not less.

My 4-Year Journey with Growing Data

I migrated from GnuCash to Beancount in 2022, and at the time I worried I was over-engineering things. “Do I really need plain text for my simple personal finances and rental properties?”

Four years later with 30k+ transactions tracked:

  • GnuCash would be painfully slow by now (it was struggling at year 3)
  • Excel would be hitting limits for multi-year analysis
  • Commercial apps would have locked me into proprietary formats
  • Beancount? Still opens instantly, still fully scriptable, still completely under my control

The Newcomer Paradox

Here’s what I see in the community all the time:

Newcomers worry: “Beancount seems complex. Will it scale to my needs?”

Reality: The people who need scalability the most are the ones asking this question! If you’re worried about scaling, you probably have (or will have) enough complexity that plain text’s advantages will save you massive time.

The CLI intimidates people, but it’s exactly what makes high-volume work manageable.

But Let’s Be Honest About Trade-Offs

I want to balance the enthusiasm here with some realism:

Plain text accounting scales technically, but requires investment:

  1. Learning curve is real - You need to understand:

    • Plain text syntax
    • Version control (Git basics)
    • At least some scripting (even if just running others’ scripts)
    • Financial accounting concepts

  2. Automation takes upfront time - @bookkeeper_bob’s automation scripts didn’t write themselves. That represents real investment before the payoff arrives.

  3. The breaking point is workflow, not performance - I’ve never heard of Beancount choking on transaction volume. But I have seen people struggle when their financial life gets complex enough that they need features Beancount doesn’t have built-in.

When Plain Text Really Shines

The sweet spot for Beancount at scale:

  • You have enough volume that manual work is painful (100+ transactions/month minimum)
  • You need multi-year analysis (compound growth tracking, trend analysis, tax planning)
  • You value control and transparency (no black box, no vendor lock-in)
  • You’re willing to script (even basic Python makes huge difference)

If you’re tracking 20 transactions/month and just need simple budgeting? YNAB is probably easier. But if you’re doing serious financial tracking with growing complexity? Plain text + Python is the endgame, not a starting point you’ll outgrow.

The Question Nobody Asks

@finance_fred asked the right question: “Can anything ELSE handle high volume as well as plain text + Python?”

After 4 years, my answer is no. Excel has limits. GUI software gets slow. Commercial apps lock you in. Plain text with programming tools? It’s the only approach that genuinely scales to whatever you need.

The initial investment pays compound returns as your data grows.