Show HN: Excel to Python Compiler (pyoneer.ai)

Totally on the roadmap, but not sure when yet!

The problem is data gets into these mega-excels through all sorts of funky routes... and I really do mean funky :)

1. PowerQuery: this is defined statically in the notebook so is detectable by Pyoneer. But I don't know a ton about the integration in Python here. I imagine this is doable.

2. Manual data entry: Pyoneer can't detect this from a static Excel file, really - what's the difference between the static Excel sheet and data updates ever time? Oftentimes, users with a lot of manual data entry to "automate this in Python" by turning an Excel file into like a form. Generating a proper web app out of the Excel file would be pretty sweet!

3. Database output copied - aka, copy in a table. This one is sometimes pretty crazy - I've seen Excel workbooks that have SQL queries just copied and pasted into a random cell in the notebook, so you can copy that and run it on some archaic SQL server. And then copy the output back in...

4. Macros: runs an API call, or an SQL query, or pulls (and then formats) data from another Excel sheet. Then put it in the right place. This then requires translating Macros - which are a whole programming language of their own. This is actually pretty high-priority for us right now, based on early feedback from developers who are in the thick of it with big Excel files.

6. Custom plugins. Big finance shops build/buy plugins that pull in data all the time! We haven't really started investigating how to handle these.

5. Other workbooks: at large banks, there's an additional dependency graph of workbooks that rely on eachother across the org. It's epic. There's a single workbook that defines all market holidays, that's used for all excel files that do performance reporting. And then these performance reports feed into other Excel's (by way of direct references, but also by way of copy and pasting, but also by way of uploading/downloading through a database). Support multiple Excel files at once is something we'll have to tackle eventually!

So... there's a lot to do here. We're really early - so we're focused on two primary things right now:

1. Solving the most pressing pain points first. Hence the early launch so we can talk to more folks and prioritize better. I've got a reasonable idea since I've done so much of this work myself, but every finance shop does things different...

2. Leaving good TODOs when we can't translate something. Currently, we can't translate pivot tables or complex formulas -- but we generate TODOs for these so you can go back and fill them in with the Python skills you have (and maybe the help of ChatGPT).

We're aiming to just give you a Python script. So if we don't translate the data pull how you want... you can just edit the notebook :)

Additional thoughts:

How are you modeling the input data? Are you using dataclasses and type annotations? something like https://docs.pydantic.dev/latest/ ?

How/Where is the data stored? Not inside the notebook, right?

From their website it sounds like they know there is a market for this because they’ve been doing it manually for a while:

“Our founding team spent the past decade transitioning Excel files to Python - from startups to insurance companies to bulge bracket banks.”

> if you're a Microsoft shop like 99% of the corporate world I think you are turning those excel files into power apps and powerBI dashboards, before you are hiring python devs.

This is a good incremental decision, but once everyone's done it, perhaps an edge can be found by doing it properly. As much as IT folk love administrating Microsoft products, the products are terrible for the users.

> What customer discovery have you done so far?

Me and my two cofounders spend the past 4 years working on Mito (https://trymito.io) -- where our customers are primarily large finance shops (including some bulge bracket banks you've heard of) that has a really concrete goal of getting users out of Excel and into Python. It's not every finance shop, but a quite a few are trying to make this transition. This usually means: multi-day Python trainings, a Python support team, a few developers who semi-full-time job is helping transition existing Excel processes to Python.

We built Mito to be a tool for the Excel-first users - we tried to make it easier for them to use their existing spreadsheet skills to write Python. But in working with the developers that support these new Python users, it became clear to us that there's a big pain point around:

1. I'm a dev who was given a big, old Excel file

2. It has a lot of business logic in it, understood by the person who made it, but not by me - who is tasked with turning it into real software

3. I have to spend 100s of hours: trying to understand the file, faithfully replicating the logic, and testing for consistency - to convert this to an Excel process.

I personally have been this developer in quite a few cases - just as support for Mito and helping these Excel users trying to transition to Python. Some Excel files literally take 300+ hours to "rebuild from scratch" in Python. It's often very engaging work, but brutally slow - so we're trying to automate as much as we can with Pyoneer.

> There's a vanishing window for stuff like this, if you're a Microsoft shop like 99% of the corporate world I think you are turning those excel files into power apps and powerBI dashboards, before you are hiring python devs.

I think this is a really fair point! We're not sure exactly what a reasonable business model really looks like, long-term. Right now, we're really focused on finding the developers for-which this is a big pain point, and seeing what we need to prioritize to make their lives better. I'm one of those developers...

'I'm curious how many people actually have this problem. It strikes me that the corporate environments that are building big hairy excel files probably have locked down IT where running python is a difficult thing to do.'

FWIW I have worked at 3 financial companies (including my current job) where Excel is used heavily and where "desk quants" are allowed to use Python.

You 100% own all Python code that you download from Pyoneer. Sorry for the lack of clarity here!

You're welcome to: 1. Edit it to fix up places where it can't translate fully. 2. Send it to your colleagues and tell them you wrote it (although... for your own personal morals... maybe don't :) ) 3. Upload it to your companies Github 4. Whatever the hell else you want

To be very clear: this is your code!

The code we generate currently has no dependencies on anything other than pandas, numpy, the Python core library, and the Excel file you uploaded in the first place. This might change as we try and support more Excel features (and so need to do some pivot table mocking), but we're not aiming to lock folks in!

P.S. If you do anything particularly interesting with the code you generate... tell me about it. nate @ sagacollab.com. I'd love to hear P.P.S. I'll update the language on this ASAP.

Thanks for the link! We looked into existing libraries for Excel formula execution - this library as an awesome example!

We considered using this, but to copy from our MVP spec:

The easiest thing to do is to replicate Excel’s execution engine — you can see someone who has done this [here](https://pypi.org/project/xlcalculator/). But this is just evaluating Excel formulas is not what users want when transitioning a process to Excel - they want to be able to ditch Excel (mostly).

The next easiest thing would be to transpile Excel formulas to the following format:

    # 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.

In general: *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.

This jumped out for me too. Here seems like a huge wasted opportunity.

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!

Yes op should use formula ast and excel model via formulas/excel/plot to translate syntactically to make sure its correctness, justifying the effort.

Yep - this is a one-way process! You can think of it like an eject from Excel, in the best case.

The devs we've worked with so far have the goal of replacing the Excel process - inheriting it from the team that runs it manually, and automating it fully in Python. From them on, changes to the process would run through a more traditional software-development lifecycle, as you would be editing code.

For these devs - this is a feature not a bug! In Excel, version control, testing, and review is pretty much non-existent...

Cool username btw...

Thanks for the feedback. I've updated the landing page to prominently display this language - see the how it works section.

Why is it “very uncool”?

Woof. Always the words you stare at the most that are wrong... will update that demo video when I get the chance, but might be a bit :)

Good thing Pyoneer generates test cases for the formulas it generates! No need to trust my spelling abilities -- your Excel file is the ultimate source of truth.

Hey Jeff, thanks for the kind words. I'd love to learn more about your experience and transition through that pain point -- shoot me an email at nate @ sagacollab . com if you want to chat.