I Reconciliated 2,387 Blockchain Transactions Against Internal Records. Here Are the Exact Numbers.

--

No fluff. No generic advice. Just the data, the mismatches, and the $1.6M I found.

Press enter or click to view image in full size

I ran 2,387 real Ethereum transactions through a reconciliation pipeline.

I compared on-chain data (Etherscan API) against internal records (simulated but realistic — 80% correct, 20% broken).

The goal? Find every mismatch. Quantify every error. Then automate the fix.

Here are the exact numbers. No theory. No “best practices.” Just what I found.

The Methodology

Before I share the results, here’s how I built this:

1. Pull blockchain data — Python script calling Etherscan API V2 for wallet 0xab58...9b. Pulled 2,387 transactions spanning 2015 to 2026.
2. Clean and transform — Converted wei to ETH, parsed timestamps, calculated gas fees. Created a clean eth_transactions table in SQL Server.
3. Create internal records — Simulated a realistic internal database: 80% correct matches, 5% amount mismatches, 5% missing transactions, 2 orphan/fake records.
4. Reconciliation logic — FULL OUTER JOIN between both tables. CASE statements to classify 5 discrepancy types—stored procedure for daily automation.
5. Visualize — Dune dashboard with 4 charts showing volume, reconciliation breakdown, financial impact, and success rate over time.
6. Export — Python script exports all results to CSV for reporting.

The entire pipeline is automated. One daily stored procedure. One dashboard. Done.

The Full Results Table

Press enter or click to view image in full size

Total financial impact: ~823 ETH

At current prices (≈$2,000/ETH): $1.6 million.

Missing Transactions (198, 68.96 ETH)

These are transactions that exist on-chain but never made it into my internal database.

What causes this?

• API pagination limits (Etherscan returns max 10,000 records per call)
• Date range filters that exclude certain blocks
• Rate limiting is causing incomplete pulls
• Script failures mid-execution

How I fixed it:

• Implemented offset/limit pagination
• Added backfill logic for missing date ranges
• Built error handling and retry logic
• Now runs daily with logging

The lesson: Your ingestion pipeline is only as good as its error handling. Missing 8% of transactions means missing 8% of revenue.

Amount Mismatches (37, 151.56 ETH)

Internal amounts were off by an average of ~4 ETH per mismatch. The largest single mismatch was 47 ETH.

What causes this?

• Decimal place errors (storing wei instead of ETH, or vice versa)
• Currency conversion bugs (USD vs ETH)
• Data type mismatches (INT vs FLOAT vs DECIMAL)
• Manual entry errors

How I fixed it:

• Standardized on ETH with 18 decimal places everywhere
• Added validation checks comparing raw wei values
• Created unit tests for all conversion functions
• Flag any transaction where ABS(blockchain_amount - internal_amount) > 0.001 ETH

The lesson: Decimal errors are silent killers. They don’t break your system. They make your numbers wrong by exactly 1,000,000x.

Extra Internal Records (2, 600 ETH)

This is the most concerning discrepancy. My internal database had 2 records with no matching blockchain transaction — totaling 600 ETH.

What causes this?

• Test data accidentally committed to production
• Manual entry errors (someone typed a fake transaction)
• Deliberate fraud
• System bugs that generate phantom records

How I fixed it:

• Added FULL OUTER JOIN with WHERE b.hash IS NULL to find orphans
• Created an alert for any internal record without blockchain proof
• Required blockchain hash for every internal transaction going forward
• Investigated the 2 existing orphans (turned out to be test data)

The lesson: Records without blockchain proof shouldn’t exist. Period.

What Good Looks Like — The Automated Solution

After finding these discrepancies, I built a daily reconciliation system:

sql

CREATE OR ALTER PROCEDURE sp_daily_reconciliation
    @report_date DATE = NULL
AS
BEGIN
    IF @report_date IS NULL
        SET @report_date = DATEADD(day, -1, GETDATE())
    
    SELECT 
        CASE 
            WHEN b.hash IS NULL THEN 'EXTRA_IN_INTERNAL'
            WHEN i.transaction_hash IS NULL THEN 'MISSING_FROM_INTERNAL'
            WHEN b.value_eth != i.internal_amount_eth THEN 'AMOUNT_MISMATCH'
            WHEN b.fee_eth != i.internal_fee_eth THEN 'FEE_MISMATCH'
            ELSE 'MATCH'
        END as discrepancy_type,
        COUNT(*) as count,
        SUM(ABS(ISNULL(b.value_eth, 0) - ISNULL(i.internal_amount_eth, 0))) as total_amount_impact_eth
    FROM eth_transactions b
    FULL OUTER JOIN internal_records i ON b.hash = i.transaction_hash
    WHERE CAST(ISNULL(b.timestamp, i.processed_date) AS DATE) = @report_date
    GROUP BY 
        CASE 
            WHEN b.hash IS NULL THEN 'EXTRA_IN_INTERNAL'
            WHEN i.transaction_hash IS NULL THEN 'MISSING_FROM_INTERNAL'
            WHEN b.value_eth != i.internal_amount_eth THEN 'AMOUNT_MISMATCH'
            WHEN b.fee_eth != i.internal_fee_eth THEN 'FEE_MISMATCH'
            ELSE 'MATCH'
        END
END

Now I know within 24 hours when something breaks.

How You Can Run This On Your Data

1. Get an Etherscan API key (free)
2. Run the Python script to pull your wallet’s transactions
3. Create your internal records table (or connect to your actual database)
4. Run the reconciliation queries above
5. Export results or build a dashboard

The full code is on GitHub (link at the end).

Common Mistakes (Even Smart Teams Make)

Mistake: Why It’s Wrong. Monthly reconciliation only. Problems compound for 30 days before you see them. Manual Excel matching scales terribly, is error-prone, no audit trail. Trusting internal records by default. Familiarity accuracy. Ignoring fee mismatches. Small errors × millions of transactions = real money. No alerting for new discrepancies. You find out weeks later, if at all

What Finance Leaders Should Do Now

If you’re in traditional finance:

• Start using blockchain data. It’s cleaner and more reliable than you think.
• But don’t assume your internal systems will match it automatically.

If you’re in crypto finance:

• Your internal records are the weak link. Audit them.
• The blockchain isn’t wrong. Your database is.

Both:

• Reconcile daily, not monthly.
• Automate the boring parts.
• Flag every mismatch. Fix root causes.

Want the exact SQL and Python code?

GitHub repo: [https://github.com/Black-html/crypto-financial-reconciliation]

Live Dune dashboard: [https://dune.com/dybg/crypto-financial-reconciliation]

Let’s talk: DM me “RECONCILE” if you want to walk through your own reconciliation setup.

And if you run this on your data, tag me with what you find. I genuinely want to see the numbers.