6 ms·

A Fast Excel Formula Parser and Evaluator

106 points by EntICOnc 4y ago

greggsy 4y ago
I’m surprised that Excel formulas have such poor coverage in text editors like NP++ or VSCode. Developing horribly convoluted formulas with nested IF statements in the formula bar is painful, and the PowerQuery editor doesn’t even have syntax highlighting. Even the ancient built-in VBA IDE has better support. Surely Excel formulas are the most commonly across the whole population of computer users?
- BiteCode_dev 4y ago
  Yes, but those things have been historically mostly provided by volonteers, and I can understand they didn't want to spend a lot of effort trying to clean the mess of a giant corporation of Microsoft for free. MS should have provided a formulat parser and interpretter as a FOSS portable C lib decades ago. But at the time, they didn't pretend to be the good guys to win market shares, they publically despised anything open or free, so it couldn't happen. I guess it would have help OOo a lot at the time, and would help pandas today. Now that MS has VSCode, maybe they'll end up doing a LSP, but I doubt it. Programmers are not their target for excel.
- galacticdessert 4y ago
  As I consultant that works extensively in Excel, I understand your point. The lack of decent syntax highlighting, auto-indentation or “IDE mode” instead of the formula bar can be painful. Especially when working with models developed by others. On the other hand, you can see it as a feature. It forces you to keep your formulas short, break them down in helper columns and overall make your logic cleaner. If you are writing a 4 line long formula, you are doing something wrong. Sytax highlighting would still surely help, but it is not a replacement for clean modelling.
  - 2b3a51 4y ago
    Amateur user of Excel/Calc/Google sheets here. My worst is... =CONCATENATE( IF(A1<0;"-";"+"); TEXT(INT(ABS(A1));"00") ;"d"; IF((ABS(A1) - INT(ABS(A1)) - INT( (ABS(A1) - INT(ABS(A1)))*60 )/60) * 3600 > 59.5; TEXT(INT((ABS(A1) - INT(ABS(A1)))*60)+1;"00"); TEXT(INT((ABS(A1) - INT(ABS(A1)))*60);"00") );"m"; IF((ABS(A1) - INT(ABS(A1)) - INT( (ABS(A1) - INT(ABS(A1)))*60 )/60) * 3600 > 59.5; TEXT(0;"00"); TEXT(ROUND( (ABS(A1)-INT(ABS(A1))-INT((ABS(A1)- INT(ABS(A1)))*60)/60)*3600;0 );"00") );"s" ) ...which should take a value in cell A1 that is in the range [0,360) and return the value in angle notation (degrees, minutes and seconds). I especially wanted this function all in one cell to save loads of helper cells (the spreadsheet calculates the position of the moon, sun, planets and a physical ephemeris for each planet so a lot of angles to display). It is a pain to apply, I use a text editor to search and replace the cell reference for each angle. I'd settle for the formula bar display of the formula not reformatting the line breaks.
    - ReactNative22 4y ago
      You could put that into a VBA formula, although it wouldn't be as fast unfortunately (as I learned when I tried simplifying excel formulae - which are _compiled_ whereas VBA I think is interpreted? - in any case, excel was like 50x faster for me). I assume you have tried the '$' signs to see if they will help?
      - 2b3a51 4y ago
        Thanks for reply. Yes, if it was just for Excel I'd make a user defined function that returned a text value. I wanted the formula to work the same on Libre/Open office and on Google sheets so I went for an actual spreadsheet formula. Actually, the ';' separator came from the LibreOffice version. '$' signs for A1? I'd still need to search and replace for A1 each time I needed to convert an angle in a different cell but I take your point that I could move the output around more easily. Speed does not seem to be an issue (the rest of the spreadsheet has calls to trig functions by the score)
        4y ago
  - snthpy 4y ago
    In the old Excel with only cell grid references I would agree. However in "new Excel" with LET expressions and LAMBDA it is now possible to write legible and self-documenting code where the lack of decent editing support is becoming a problem.
  - grvdrm 4y ago
    Wish I could +1 this 100x. Break into small clean chunks that are easy to follow.
  - deleted 4y ago
    [deleted]
  - lttlrck 4y ago
    Related: https://www.microsoft.com/en-us/garage/blog/2022/03/a-new-way-to-author-and-share-excel-named-formulas-advanced-formula-environment-a-microsoft-garage-project/ https://www.microsoft.com/en-us/garage/blog/2022/03/a-new-wa...
    - greggsy 4y ago
      Wow that’s actually kind of cool.
- andylynch 4y ago
  It’s really new but the Advanced Formula Editor[1] tackles this, and goes further. It’s a collaboration between the Excel team and MS’ Cambridge boffins. [1] https://www.microsoft.com/en-us/garage/blog/2022/03/a-new-way-to-author-and-share-excel-named-formulas-advanced-formula-environment-a-microsoft-garage-project/ https://www.microsoft.com/en-us/garage/blog/2022/03/a-new-wa...
- masklinn 4y ago
  > Surely Excel formulas are the most commonly across the whole population of computer users? Maybe, but of developers who’ve installed an editor? And would go through copying formulas back and forth? Surely at that point you’re using vba, or you’ve moved to scripting excel from the outside, or off of excel entirely?
  - onychomys 4y ago
    Some of us have jobs where we write code 50% of the time and the other 50% is yelling at the biologists that work in our labs to please for the love of god stop doing so much stuff with excel formulas.
ReactNative22 4y ago
Python and Pandas > Excel for anything outside of exploratory spreadsheet-y like calculations (I work frequently with 200k rows + of data, I _had_ to learn Python as Excel was too slow eventually and broke...now having a programming language to do formulae, I can't go back for any of my production processes, using excel now mostly for pivot tables and exploratory analysis (Excel is pretty awesome at that)
- mritchie712 4y ago
  you might like bamboo https://bamboolib.8080labs.com/ https://bamboolib.8080labs.com/
  - infotropy 4y ago
    It seems interesting but their acquisition by Databricks stopped development on Bamboo last year.
- kmac_ 4y ago
  Well, yes and no - nothing can compete with Excel in terms of modeling speed and agility. A capable analyst can do miracles with Excel, find other routes to a solution, and then produced model can go to Python/Pandas developer for the processing optimization.
- rwalle 4y ago
  ...Of course, you need to do professional data manipulation (whether in Python or with a real database) for 200k rows of data. Excel is never meant for handling that amount of information
- cyanydeez 4y ago
  Excel > * for communicating with anyone not a data scientist and still knows what they're doing
- narush 4y ago
  Hiya! I'm a cofounder of Mito [1] - where a spreadsheet extension to Jupyter Notebooks / Lab. Our most used feature is our exploratory graphing and Excel-like pivot tables - which seems super relevant. Totally no pressure (although this is clearly a pitch), but if you get the chance to check it out and have feedback on where we fall short of being as awesome as Excel, your feedback would be super appreciated! [1] https://trymito.io https://trymito.io
robbiejs 4y ago
Looks great! I have received many requests to add formulas to my grid product DataGridXL (https://datagridxl.com https://datagridxl.com) and this looks excellent. Starred :-)
- pessimizer 4y ago
  With Excel formula compatibility, a js Range object (allowing people to keep track of multiple cursors on spreadsheets and to create relationships between multiple spreadsheets in a document), a way to define new functions in js that would work in formulas, and Excel import/export (sans macros/vba), there'd be no need for Excel for me. edit: just making sure, don't be offended - https://docs.microsoft.com/en-us/office/vba/api/Excel.Range(object) https://docs.microsoft.com/en-us/office/vba/api/Excel.Range(...
maxpert 4y ago
I wish someone can do a Rust/C based implementation for this so that I can use it in other languages.
SloopJon 4y ago
I've always been fuzzy on Excel's precision. It's frequently described as preserving or storing fifteen significant figures. I wasn't sure if it rounds before storing, or if that's just how it displays. After some experimentation, I've satisfied myself that it can store more than it can display. For example, =PI() displays at most 3.14159265358979, no matter how many digits I ask it to display. If I add 1.999E-15, I get 3.1415926535898. If I type 3.14159265358979 (omitting the invisible trailing 3), then add 1.999E-15, I get 3.14159265358979. In other words, Excel uses double-precision floating point, but never displays more than fifteen significant figures. You may be in for a surprise if, say, you round trip a spreadsheet through CSV.
- bitmedley 4y ago
  Excel uses IEEE 754 double precision which stores up to 17 significant digits but the Excel UI itself only displays up to 15 significant digits. [0] https://en.wikipedia.org/wiki/Double-precision_floating-point_format#:~:text=If%20an%20IEEE%20754%20double,must%20match%20the%20original%20number https://en.wikipedia.org/wiki/Double-precision_floating-poin.... [1] https://www.mrexcel.com/excel-tips/17-or-15-digits-of-precision/ https://www.mrexcel.com/excel-tips/17-or-15-digits-of-precis... [2] https://www.youtube.com/watch?v=WZfjmbEDbfI https://www.youtube.com/watch?v=WZfjmbEDbfI

106 points by EntICOnc 4y ago

I’m surprised that Excel formulas have such poor coverage in text editors like NP++ or VSCode. Developing horribly convoluted formulas with nested IF statements in the formula bar is painful, and the PowerQuery editor doesn’t even have syntax highlighting. Even the ancient built-in VBA IDE has better support. Surely Excel formulas are the most commonly across the whole population of computer users?

BiteCode_dev 4y ago
Yes, but those things have been historically mostly provided by volonteers, and I can understand they didn't want to spend a lot of effort trying to clean the mess of a giant corporation of Microsoft for free. MS should have provided a formulat parser and interpretter as a FOSS portable C lib decades ago. But at the time, they didn't pretend to be the good guys to win market shares, they publically despised anything open or free, so it couldn't happen. I guess it would have help OOo a lot at the time, and would help pandas today. Now that MS has VSCode, maybe they'll end up doing a LSP, but I doubt it. Programmers are not their target for excel.
galacticdessert 4y ago
As I consultant that works extensively in Excel, I understand your point. The lack of decent syntax highlighting, auto-indentation or “IDE mode” instead of the formula bar can be painful. Especially when working with models developed by others. On the other hand, you can see it as a feature. It forces you to keep your formulas short, break them down in helper columns and overall make your logic cleaner. If you are writing a 4 line long formula, you are doing something wrong. Sytax highlighting would still surely help, but it is not a replacement for clean modelling.
- 2b3a51 4y ago
  Amateur user of Excel/Calc/Google sheets here. My worst is... =CONCATENATE( IF(A1<0;"-";"+"); TEXT(INT(ABS(A1));"00") ;"d"; IF((ABS(A1) - INT(ABS(A1)) - INT( (ABS(A1) - INT(ABS(A1)))*60 )/60) * 3600 > 59.5; TEXT(INT((ABS(A1) - INT(ABS(A1)))*60)+1;"00"); TEXT(INT((ABS(A1) - INT(ABS(A1)))*60);"00") );"m"; IF((ABS(A1) - INT(ABS(A1)) - INT( (ABS(A1) - INT(ABS(A1)))*60 )/60) * 3600 > 59.5; TEXT(0;"00"); TEXT(ROUND( (ABS(A1)-INT(ABS(A1))-INT((ABS(A1)- INT(ABS(A1)))*60)/60)*3600;0 );"00") );"s" ) ...which should take a value in cell A1 that is in the range [0,360) and return the value in angle notation (degrees, minutes and seconds). I especially wanted this function all in one cell to save loads of helper cells (the spreadsheet calculates the position of the moon, sun, planets and a physical ephemeris for each planet so a lot of angles to display). It is a pain to apply, I use a text editor to search and replace the cell reference for each angle. I'd settle for the formula bar display of the formula not reformatting the line breaks.
  - ReactNative22 4y ago
    You could put that into a VBA formula, although it wouldn't be as fast unfortunately (as I learned when I tried simplifying excel formulae - which are _compiled_ whereas VBA I think is interpreted? - in any case, excel was like 50x faster for me). I assume you have tried the '$' signs to see if they will help?
    - 2b3a51 4y ago
      Thanks for reply. Yes, if it was just for Excel I'd make a user defined function that returned a text value. I wanted the formula to work the same on Libre/Open office and on Google sheets so I went for an actual spreadsheet formula. Actually, the ';' separator came from the LibreOffice version. '$' signs for A1? I'd still need to search and replace for A1 each time I needed to convert an angle in a different cell but I take your point that I could move the output around more easily. Speed does not seem to be an issue (the rest of the spreadsheet has calls to trig functions by the score)
      - 4y ago
- snthpy 4y ago
  In the old Excel with only cell grid references I would agree. However in "new Excel" with LET expressions and LAMBDA it is now possible to write legible and self-documenting code where the lack of decent editing support is becoming a problem.
- grvdrm 4y ago
  Wish I could +1 this 100x. Break into small clean chunks that are easy to follow.
- deleted 4y ago
  [deleted]
- lttlrck 4y ago
  Related: https://www.microsoft.com/en-us/garage/blog/2022/03/a-new-way-to-author-and-share-excel-named-formulas-advanced-formula-environment-a-microsoft-garage-project/ https://www.microsoft.com/en-us/garage/blog/2022/03/a-new-wa...
  - greggsy 4y ago
    Wow that’s actually kind of cool.
andylynch 4y ago
It’s really new but the Advanced Formula Editor[1] tackles this, and goes further. It’s a collaboration between the Excel team and MS’ Cambridge boffins. [1] https://www.microsoft.com/en-us/garage/blog/2022/03/a-new-way-to-author-and-share-excel-named-formulas-advanced-formula-environment-a-microsoft-garage-project/ https://www.microsoft.com/en-us/garage/blog/2022/03/a-new-wa...
masklinn 4y ago
> Surely Excel formulas are the most commonly across the whole population of computer users? Maybe, but of developers who’ve installed an editor? And would go through copying formulas back and forth? Surely at that point you’re using vba, or you’ve moved to scripting excel from the outside, or off of excel entirely?
- onychomys 4y ago
  Some of us have jobs where we write code 50% of the time and the other 50% is yelling at the biologists that work in our labs to please for the love of god stop doing so much stuff with excel formulas.

ReactNative22 4y ago

Python and Pandas > Excel for anything outside of exploratory spreadsheet-y like calculations (I work frequently with 200k rows + of data, I _had_ to learn Python as Excel was too slow eventually and broke...now having a programming language to do formulae, I can't go back for any of my production processes, using excel now mostly for pivot tables and exploratory analysis (Excel is pretty awesome at that)

mritchie712 4y ago
you might like bamboo https://bamboolib.8080labs.com/ https://bamboolib.8080labs.com/
- infotropy 4y ago
  It seems interesting but their acquisition by Databricks stopped development on Bamboo last year.
kmac_ 4y ago
Well, yes and no - nothing can compete with Excel in terms of modeling speed and agility. A capable analyst can do miracles with Excel, find other routes to a solution, and then produced model can go to Python/Pandas developer for the processing optimization.
rwalle 4y ago
...Of course, you need to do professional data manipulation (whether in Python or with a real database) for 200k rows of data. Excel is never meant for handling that amount of information
cyanydeez 4y ago
Excel > * for communicating with anyone not a data scientist and still knows what they're doing
narush 4y ago
Hiya! I'm a cofounder of Mito [1] - where a spreadsheet extension to Jupyter Notebooks / Lab. Our most used feature is our exploratory graphing and Excel-like pivot tables - which seems super relevant. Totally no pressure (although this is clearly a pitch), but if you get the chance to check it out and have feedback on where we fall short of being as awesome as Excel, your feedback would be super appreciated! [1] https://trymito.io https://trymito.io

robbiejs 4y ago

Looks great! I have received many requests to add formulas to my grid product DataGridXL (https://datagridxl.com https://datagridxl.com) and this looks excellent. Starred :-)

pessimizer 4y ago
With Excel formula compatibility, a js Range object (allowing people to keep track of multiple cursors on spreadsheets and to create relationships between multiple spreadsheets in a document), a way to define new functions in js that would work in formulas, and Excel import/export (sans macros/vba), there'd be no need for Excel for me. edit: just making sure, don't be offended - https://docs.microsoft.com/en-us/office/vba/api/Excel.Range(object) https://docs.microsoft.com/en-us/office/vba/api/Excel.Range(...

maxpert 4y ago

I wish someone can do a Rust/C based implementation for this so that I can use it in other languages.

SloopJon 4y ago

I've always been fuzzy on Excel's precision. It's frequently described as preserving or storing fifteen significant figures. I wasn't sure if it rounds before storing, or if that's just how it displays. After some experimentation, I've satisfied myself that it can store more than it can display. For example, =PI() displays at most 3.14159265358979, no matter how many digits I ask it to display. If I add 1.999E-15, I get 3.1415926535898. If I type 3.14159265358979 (omitting the invisible trailing 3), then add 1.999E-15, I get 3.14159265358979. In other words, Excel uses double-precision floating point, but never displays more than fifteen significant figures. You may be in for a surprise if, say, you round trip a spreadsheet through CSV.

bitmedley 4y ago
Excel uses IEEE 754 double precision which stores up to 17 significant digits but the Excel UI itself only displays up to 15 significant digits. [0] https://en.wikipedia.org/wiki/Double-precision_floating-point_format#:~:text=If%20an%20IEEE%20754%20double,must%20match%20the%20original%20number https://en.wikipedia.org/wiki/Double-precision_floating-poin.... [1] https://www.mrexcel.com/excel-tips/17-or-15-digits-of-precision/ https://www.mrexcel.com/excel-tips/17-or-15-digits-of-precis... [2] https://www.youtube.com/watch?v=WZfjmbEDbfI https://www.youtube.com/watch?v=WZfjmbEDbfI