A "financial model", in an 80 Megabyte Excel file (.xlsx) bloated with VBA macros, thousands of intertwined VLOOKUPs and hidden tabs, ends up corrupting itself. What happens to Excel is the same thing that happens to politicians, it gets corrupted by success.

I've been saying for years that using Microsoft's spreadsheet as a database or a business rule engine is a ticking time bomb, but in the corporate world, people cling to Excel as if it were a religion. Yesterday I decided to explain that this has to end: we are going to automate this like real computer scientists.

The hell of macros and VLOOKUP

The problem with Excel is not the tool itself (it's fantastic for prototyping and quick math), but the software lifecycle. A giant Excel file has no version control (who hasn't seen files named Final_Report_V3_definitive_THIS_ONE.xlsx?), it doesn't separate data from presentation, and its formulas are inscrutable black boxes that cannot be tested.

I sat down with the guys who understand the Excel, we rebuilt the logic of what their famous file did starting from an old backup, and I ported it to code.

Python to the rescue

The business logic was surprisingly simple: extract data from three different tables, cross-reference them, and apply a commission calculation. Instead of making Excel suffer by crossing 500,000 rows in memory, I delegated the heavy lifting to our Data Warehouse and wrote a Python script to generate the report automatically.

Using the standard csv library (and the wonderful pyodbc connection for SQL Server), the process is transparent, fast, and foolproof:

import pyodbc
import csv

# Conexión directa a nuestro SQL Server
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=servidor_dw;DATABASE=Analitica;UID=uid;PWD=secreto')
cursor = conn.cursor()

# El motor de base de datos hace el trabajo pesado
query = """
    SELECT 
        c.Nombre, 
        SUM(v.Importe) as TotalVentas,
        SUM(v.Importe) * 0.05 as ComisionCalculada
    FROM Fact_Ventas v
    JOIN Dim_Comerciales c ON v.IdComercial = c.IdComercial
    WHERE v.Fecha >= '2012-01-01'
    GROUP BY c.Nombre
"""

cursor.execute(query)

# Volcamos directamente a un CSV limpio que no se corrompe
with open('reporte_comisiones_generado.csv', 'wb') as f:
    writer = csv.writer(f)
    writer.writerow([column[0] for column in cursor.description]) # Cabeceras
    for row in cursor.fetchall():
        writer.writerow(row)

print "Reporte generado en 0.5 segundos. Adiós VBA."

Reflection: The eternal battle

I know this is a half-lost battle. Business users need the flexibility to fiddle with cells, and we IT folks want rigidity and control. Visual tools like QlikView or Tableau are helping to build a bridge, but the instinct to "download to Excel" is burned into the corporate DNA. I guess we'll keep rescuing corrupted .xlsx files until the end of time, but at least it's necessary to set boundaries.