Comment by narush | Hacker News Reader

Comment by narush | Hacker News Reader

Parent Full thread

Copying and pasting from a comment below -- we considered this heavily during our MVP, but in practice "moving an Excel process to Python" does not just mean executing an Excel file through Python. This pretty much doesn't replace your Excel dependence at all.

Consider the following (pretty easy) translation of a simple table:

    # This is not a useful or usable artifact; you're still trapped in Excel
    # but things are just worse now, since it's not on a 2d grid
    A1 = "Prices"
    A2 = 1
    A3 = 2
    B1 = "With Tax"
    B2 = SUM(A1, 10) * 1.3
    B3 = SUM(A2, 10) * 1.3

But this is ultimately is an awful solution for the user: 1. It’s 100% impossible to read. A large Excel file often has 100k+ formulas (many of them with shared structures). This is 100k+ lines of code... 2. It’s impossible to maintain. Yeah, since it lacks all semantic structure, there’s no f* way you’re going to test it or modify it.

To make it really concrete: you can't just transpile a SUMIF or VLOOKUP to a Python implementation of SUMIF or VLOOKUP, and you absolutely can't do this on a cell-by-cell basis.

Rather: we're trying to generate a Python script that appears to be written by an expert developer. To do this, you have to be willing to ditch the Excel formulas / execution engine, do more abstract reasoning over the file (like identifying tables / consistent formulas in columns and translating them as pandas dataframes), and translate them without just relying on matching the Excel exactly.

You want something closer to this:

    df = pd.DataFrame({'Prices': [1, 2]})
    df['With Tax'] = (df['Prices'] + 10) * 1.3

We want parity of outputs, not parity in how we get there!

> We want parity of outputs, not parity in how we get there!

To be clear, though, you don't necessarily have parity of outputs.

It seems strange to me to sacrifice correctness for readable output. I would prefer a deterministic strategy that is always correct and sometimes readable. You could do that by generating an intermediate structure A1=... A2=..., then applying heuristics to say "hey, this enormous column of VLOOKUPs is actually a join", and so on. Maybe LLMs could advise on that, but I'm not sure how you'd check their work...

... Anyway you're the person "in the arena", having actually created something, so well done!

> To be clear, though, you don't necessarily have parity of outputs.

The cool thing is that the Excel file is both the programatic specification of the process as well as the actual output data you want as well. We can check parity of outputs by comparing the data we create with Python to the data in Excel - in practice, Pyoneer generates test cases for tables that do exactly this, even when we can't translate every formula correctly!

> applying heuristics to say "hey, this enormous column of VLOOKUPs is actually a join", and so on.

We do this deterministically currently. The only non-deterministic aspect is formula translation - where we defer to some LLM. Structurally, everything is deterministic though - and here we really do aim for readability (there's a lot more to do here though).

> we're trying to generate a Python script that appears to be written by an expert developer.

But you're not. You're just deferring to an LLM. Try to create some abstractions that can work. For instance there are libraries that unuglify code. Better to start from correct python and build from that than doing the whole shebang with an LLM black box

In your case of 100k formulas verifying the output is correct would be a monumental task especially if it's not deterministic and you can't have library tests. And the human readable format is much harder to deduce about and validate. And getting an answer for one a single input output pair is prone to over fitting esp if you do it recursively to have it fit errors

No offense, but I can't imagine anyone using this for anything serious.

Reply on news.ycombinator.com