5 ms·

Show HN: Excel to Python Compiler

79 points by narush 2y ago

We (me and @aarondia) built a tool to help you turn psuedo-software Excel files into real-software Python. Ideally, Pyoneer helps you automate your manual Excel processes. You can try it today here: https://pyoneer.ai https://pyoneer.ai. How it works: 1. You upload an Excel file 2. We statically parse the Excel file and build a dependency graph of all the cells, tables, formulas, and pivots. 3. We do a graph traversal, and translate nodes as we hit them. We use OpenAI APIs to translate formulas. There’s a bunch of extra work here — because even with the best prompt engineering a fella like me can do, OpenAI sucks at translating formulas (primarily because it doesn’t know what datatypes its dealing with). We augment this translation with a mapping from ranges to variable names and types, which in our experience can improve the percentage of correctly translatable formulas by about 5x. 4. We generate test cases for our translations as well, to make sure the Python process matches your Excel process. 5. We give you back a Jupyter notebook that contains the code we generated. If there are pieces of the Excel we can’t translate successfully (complex formulas, or pivot tables currently), then we leave them as a TODO in the code. This makes it easy for you to hop in and continue finishing the script. Who is this for: Developers who know Python, primarily! Pyoneer might be useful if: 1. You’ve got an Excel file you’re looking to move to Python (usually for speed, size, or maintenance reasons). 2. There’s enough logic contained in the notebook that it’s going to be a hassle for you to just rewrite it from scratch. 3. Or you don’t know the logic that is in the Excel workbook well since you didn’t write it in the first place :) Post translation, even if Pyoneer doesn't nail it perfectly or translate all the formulas, you'll be able to pop into the notebook and continue cleaning up the TODOs / finish writing the formulas. What the Alpha launch supports: Launched early! Currently we’re focused on supporting: 1. Any number of sheets, with any reference structure between them. 2. Cells that translate as variables directly. We’ll translate the formulas to Python code that has the same result, or else we’ll generate a TODO letting you know we failed translating this cell. 3. Tables that translate as Pandas dataframes. We support at most one table per sheet, at the tables must be contigious. If the formulas in a column are consistent, then we will try and translate this as a single pandas statement. We do not support: pivot tables or complex formulas. When we fail to translate these, we generate TODO statements. We also don’t support graphs or macros - and you won’t see these reflected in the output at all currently. Why we built this: We did YCS20 and built an open source tool called Mito(https://trymito.io https://trymito.io). It’s been a good journey since then - we’ve scaled revenue and to over 2k Github stars (https://github.com/mito-ds/mito https://github.com/mito-ds/mito). But fundamentally, Mito is a tool that’s useful for Excel users who wanted to start writing Python code more effectively. We wanted to take another stab at the Excel -> Python pain point that was more developer focused - that helped developers that have to translate Excel files into Python do this much more quickly. Hence, Pyoneer! I’ll be in the comments today if you’ve got feedback, criticism, questions, or comments.

trevzercap 2y ago
Very cool, what are your plans for working with external data sources via power query?
- narush 2y ago
  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 :)

79 points by narush 2y ago

trevzercap 2y ago

Very cool, what are your plans for working with external data sources via power query?

narush 2y ago
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 :)