[HN Gopher] Advancing Excel as a programming language [audio]
___________________________________________________________________
Advancing Excel as a programming language [audio]
Author : 0-_-0
Score : 46 points
Date : 2021-05-05 11:42 UTC (11 hours ago)
(HTM) web link (blubrry.com)
(TXT) w3m dump (blubrry.com)
| MH15 wrote:
| A slight tangent but I interned at Blubrry as a software engineer
| a few summers back. Was a great experience!
| aarondia wrote:
| After talking with Excel users for the past year to understand
| what makes it such a ubiquitous tool, I don't think that its the
| ability to write complex formulas, but instead the reactive,
| visual environment (read: IDE). Of course, for some users the
| ability to nest 100 VLookups or create UDF's is the important
| part, but for the vast majority of people, their workbooks aren't
| __that__ complex.
|
| When it comes to data analytics IDEs there is a fundamental
| tradeoff between staring at the data or staring at the
| transformations. Excel makes sure you are brutally aware of each
| edit you make to your data at the expense of reproducibility and
| auditability of your transformations. Python takes the opposite
| end of the trade -- obscuring the underlying data, but bringing
| the transformations to the forefront. For non-programmers trying
| to learn Python (especially for data analytics), the biggest
| hurdle is losing touch with their data.
|
| I've been building a Python package, Mito
| (https://trymito.io/hnc), to try to address this tradeoff for
| those who want to analyze data with the intuitiveness and data-
| first-ness of a spreadsheet, but with the power and traceability
| of Python. Mito is a Jupyter Lab extension which gives users an
| interactive spreadsheet that automatically converts your
| spreadsheet analysis to the equivalent pandas code. You can write
| Excel formulas, merge datasets, create pivot tables, etc.
| airstrike wrote:
| I've seen Mito around HN and reddit before and I think it's an
| absolutely brilliant, but its applications may be more limited
| than the vast use cases covered by Excel. Maybe that's fine, as
| your goal may *be to carve out a niche rather than compete with
| Excel more broadly.
|
| The reason I say that is I think the major pain point in its
| hypothetical widespread adoption would be getting users
| accustomed to the ergonomics of accessing attributes in a
| programming language when they only understand noob-friendly
| Excel formulas, which is more functional in nature.
|
| Looking at the demo, I think some pseudo-code such as the
| following could be easier for e.g. an office worker than pure
| python: ADDCOLUMN(Sheet='Train Stations',
| Name='Accepts Bags') # defaults to appending at the end
| SETFORMULA(Column='Train Stations'!'Accepts Bags', As=IF('Train
| Stations'!'Checked Baggage' = "Y", 1, 0))
| PIVOT(From='Train Stations', To='Pivot', Keys=('State'),
| Values=('Accepts Bags'), Formula=SUM)
| SORT(Target='Pivot', By='Accepts Bags', Direction='Asc',
| NA='Hide') # defaults to ascending, NA first
|
| Clearly, it's not like I've thought this through carefully and
| am not claiming this particular example is really ergonomic,
| but hopefully this illustrates the point I'm trying to make.
|
| It would not need to look like Excel, but I think the jump from
| spreadsheet -> Python may be a step too far for the average
| user than, say, spreadsheet -> some functional approach.
| aarondia wrote:
| Glad to hear you're a fan! You're right, Mito is not designed
| to cover all of Excel's use cases. Mito focuses on
| rectangular datasets that fit nicely into a pandas dataframe
| ... at least for now. So the types of use cases that we're
| looking for is data cleaning, pivoting, etc. If you're
| building an LBO, Mito is not the tool for you.
|
| It seems like what your proposing is almost a wrapper around
| pandas functionality to make the language easier to read for
| Excel users. I think that's a super interesting approach
| which we honestly haven't thought that much about. As a rule
| of thumb for Mito right now, any spreadsheet formula gets
| generated as a Mito formula (ie: using an IF statement in the
| Mito spreadsheet generates the code IF(A > B, 1, 0) instead
| of the Pandas code) and anything else is raw pandas code (ie:
| pivot tables, merges, add column).
|
| In general, we've been thinking about trying to move more of
| the code to the raw python approach since we've heard things
| like "not seeing the raw script makes the code
| unproductionizable" etc. But I also see your point that
| beginning Python users might prefer readable code over Python
| code. If we took that approach, users would still get the
| reproducibility, auditability, and ability to use a
| spreadsheet interface on large datasets, they'd just
| sacrifice any semblance of learning Python. That's great food
| for thought!
| samfisher83 wrote:
| Excel is probably the single most used piece of business software
| in the world. People have been able to make quake. People have
| done 3d rendering without vba. People have done all kinds of
| stuff with Excel for a while now.
| m3kw9 wrote:
| Nothing beats easy.
| sokoloff wrote:
| I strongly suspect Word is used more, probably a lot more. I
| wonder if PowerPoint is used a similar amount.
| vagrantJin wrote:
| Powerpoint is powerful once you get deep into it. I know
| people who make some crazy visuals using powerpoint and you
| would thing it was done with some adobe software.
| krono wrote:
| Is there a real difference between the classic MS office
| applications, though? You can do spreadsheets in Word and
| Outlook, have styled text in your Excel document, add
| animations to your titles in Word, etc.
|
| At this point they can just as wel get rid of the different
| GUIs facade and instead implement some contextual interaction
| model (and hopefully they'll include an API so we can
| generate these documents programatically and don't have to
| deal with this nth clippy generation :) )
| icapybara wrote:
| Might not be from MS, but automating MS Office document
| generation is a solved problem:
|
| https://python-docx.readthedocs.io/en/latest/
|
| https://www.python-excel.org/
|
| https://python-pptx.readthedocs.io/en/latest/
| krono wrote:
| Yeah this is awesome community work, and I'm honestly
| thankful for MS having left these works alone.
|
| An official solution would be nice though. These open
| source projects are popular enough to warrant one I'd
| say!
| Closi wrote:
| > Is there a real difference between the classic MS office
| applications, though?
|
| Yes, there absolutely is.
|
| You can create a document with styled text in Excel instead
| of Word, just as you can edit a photo in Paint instead of
| Photoshop. You can do it, but the two tools have
| dramatically different capabilities.
|
| It's important to think about developing for use-cases
| otherwise we will end up with overly complex software that
| aims to be all-things to all-people.
| krono wrote:
| > overly complex software that aims to be all-things to
| all-people
|
| This is exactly what I believe the current state of the
| MS Office applications to be.
|
| The interface and/or implementation isn't always ideal
| for the purpose you would generally use a specific app
| for, but the functionality is there.
|
| > dramatically different capabilities
|
| Embedding a fully functional Excel spreadsheet is only a
| few clicks through the ribbon and some frustration away
| in Outlook, Word, and even PowerPoint.
| Closi wrote:
| > Embedding a fully functional Excel spreadsheet is only
| a few clicks through the ribbon and some frustration away
| in Outlook, Word, and even PowerPoint.
|
| This is effectively opening a reduced version of excel in
| a very limited way, primarily for embedding one document
| in another and allowing limited editing. You don't get
| the full functionality of the other application.
|
| I can see why I would want to change the axis on a graph
| even after I have pasted it into my email, but why would
| I want one app to be both my spreadsheet and my email
| inbox?
|
| Are you a user of office suite? I spend about 70% of my
| work life between excel, PowerPoint and Word and have
| never once wanted them to be one app, but quite often
| have wanted better integration.
| nvrspyx wrote:
| I'm not quite sure. Within my work team, Word is only used
| occasionally, specifically for anything document related.
| Excel, on the other hand, is used all the time from typical
| number crunching to keeping track of tasks (despite how much
| I despise using it for the latter). I also imagine that small
| businesses, like restaurants, don't require Word very often,
| but a lot of them use Excel for finances, inventory, etc.
|
| In regards to PowerPoint, I imagine it lags pretty far behind
| in usage compared to both Excel and Word. Not many people are
| making presentations in the grand scheme of things. My
| intuition says that it's mostly upper management and maybe a
| single person in a group using PowerPoint "often".
| Guest42 wrote:
| For me at least, I try to avoid Word because it is so slow
| and cumbersome compared to notepad or notepad++ for the
| purpose of writing notes. It seems like most of my word
| processing is for my eyes only.
| jdgoesmarching wrote:
| I didn't realize how much I hated Word until I started
| using Pages. For notes specifically I prefer some sort of
| Markdown editor.
| Guest42 wrote:
| I'll have to give pages a try. I think perhaps I favor
| the earlier versions of office products because I learned
| on them, they're faster, and they weren't as a service.
|
| I'll go for latex if something has to be published and
| certainly use power point a bit but am always on the
| lookout for new approaches. It's great that power point
| can pull/receive graph data.
| Tagbert wrote:
| A word processor like Word is really aimed at larger, more
| complex documents with formatting.
|
| Notes are usually smaller with simpler structure and
| formatting. With notes, it helps of the app gives you a way
| to organize the notes. This is really a different use case.
| dimator wrote:
| I would wager excel is used more, because spreadsheets are
| "live" and opened/updated much more than a written document.
| sokoloff wrote:
| That's true, but I posit that there are entire large
| _categories_ of computer users who open Excel exactly 0
| times per year but who use Word.
| croes wrote:
| And is one of the largest source of problems because of
| unsupportable amateur made Excel "solutions".
| inopinatus wrote:
| People have also made megadollar blunders. As programming
| environments go, Excel carefully maximises the fuckup surface,
| enabling all users from novice to expert to stumble unawares
| into hidden calculation bugs. Cell-reference slip-ups (via
| copypaste or otherwise) are _de rigeur_ , of course, and
| they're aided and abetted by Excel's cockeyed "type" system (if
| we can even call it that), half-baked documentation,
| inconsistent function library, and mediocre/absent testing,
| integrity, versioning, or debugging tools.
|
| Probably the worst example I've seen first-hand was an entire
| retail banking loan-approval process running off of a single,
| shared, gigantic spreadsheet, that hundreds had tinkered with,
| but no-one understood or took responsibility for, and where the
| accompanying Word document of "things not to do" was bigger
| than the workbook.
|
| Even yesterday, a friend of mine discovered they'd underclaimed
| expenses for a total >$1,000 due to a dodgy spreadsheet.
| Something as simple as pasting a list of dollar amounts from a
| webpage into Excel can produce an incorrect SUM() if/when
| trailing spaces creep in, since the resulting values may be
| treated as strings and evaluate to zero - and so it had
| transpired. Not even "text to columns" could fix it; you have
| to a) know about this lurking monster, b) use formatting to
| make it casually evident, and c) use Replace to strip the
| whitespace. What a crock.
| kumarvvr wrote:
| Why not just provide a way to script Excel with Python / JS?
|
| Is it technically difficult?
| analog31 wrote:
| It's probably difficult. I'm sure Microsoft has no great love
| for VBA, after all, they effectively orphaned the language when
| they came out with VB dot net. Adding the scripting language
| _per se_ would be relatively easy. But a big part of VBA is
| being able to navigate the Excel / Office object hierarchy. I
| believe making that easy in Python would be hard.
| m16ghost wrote:
| Which version of Python/JS do they implement?
|
| I think part of the problem is that any scripting language
| rolled directly into Excel will be expected to keep backwards
| compatibility. Microsoft doesn't have full control over
| Python/JS, and they would like to avoid issues such as the
| changeover from Python 2 to 3.
|
| Microsoft could implement its own fork of those languages, but
| is that what customers actually want?
| meibo wrote:
| Scripting in Excel is a complex mess of VBA and COM, it's
| probably far from trivial to provide (sane) bindings into other
| languages.
|
| You can do it via .NET, but a lot of people prefer VBA for its
| "quick and dirty"-ness.
| jacobsenscott wrote:
| You script it with VBA
| mslip wrote:
| Something that isn't PTSD inducing would be better lol
| auxym wrote:
| It's pretty easy to use C# (or even F#) to write excel add-ins
| that can be distributed as standalone XLL files.
|
| The difference is that add-ins is installed at the application
| level instead of the spreadsheet level (like VBA macros).
| bitwize wrote:
| You can use COM/OLE to script Excel with Python or JS.
|
| I think Microsoft is making JS a first-class scripting language
| for Excel (and slowly deprecating VBA).
| ant6n wrote:
| I wish there was a Python excel.
| olav wrote:
| Just yesterday I stumbled across https://pyspread.gitlab.io/
| after finding that coding Excel is a complete desaster from a
| software engineering point of view - no testing, no
| debugging, no version control, no structure. It must have
| costed humanity many billions in vain.
| saynotobloat wrote:
| https://openpyxl.readthedocs.io/en/stable/index.html
| Vaslo wrote:
| Not that many features are applicable to Excel. I've tried
| these and eventually have to default back to VBA to do what
| I need.
| fartcannon wrote:
| With the uno library, you can directly script LibreOffice
| Calc with python. https://help.libreoffice.org/6.3/en-
| US/text/sbasic/python/ma...
|
| As a demonstration, I ran a ray tracing python script from
| Calc and it rendered to cells in the spreadsheet.
| deepstack wrote:
| there is already exceljs
| corty wrote:
| There is already VisualBasic for Applications which is just
| similar to Python, only with begin/end-blocks instead of
| indentation. And with a little fiddling you can also use
| JScript/WSH to remote-control some parts of Excel, as well as
| all the .Net languages.
| Vaslo wrote:
| I code in VBA constantly as a Finance Manager. I also spend a
| lot of time learning Python and used it throughout a CS
| Degree. VBA and Python are not alike at all IMO.
|
| You are right that you can use something like C# to
| manipulate Excel files, but there isn't a ton of tutorials on
| it, which makes it difficult and prohibitive to learn.
| kumarvvr wrote:
| > which is just similar to Python
|
| What? Am I the only one who hates VBA with a burning hatred
| of a thousand suns?
| andy81 wrote:
| That's a common opinion.
|
| The object model and IDE are a big part of it though, maybe
| even more so than syntax horrors like while..wend vs do
| while..loop.
| corty wrote:
| The libraries in VisualBasic (before .net) do suck. But
| syntactically, Python and Basic are very close. I hate both
| very much, because all the nice features that make
| programming fun are lacking. Both feel very much like using
| a childs' toy instead of proper tools.
| auxym wrote:
| VBA is very 90s. As in, similar to 90s era java, right up
| to the built-in IDE.
| selimthegrim wrote:
| Blockpad (https://blockpad.net) is a nice engineering focused
| alternative that shoots for this (with a JS like DSL)
| goatlover wrote:
| You can generate Excel files in Python with XlsxWriter. It has
| an API for formatting, charts, merging cells and what not. If
| you want to automate an open Excel file, you can use xlwings.
| You can write data to Excel from pandas inside a Jupyter
| notebook.
| ww520 wrote:
| Scripting with Excel has two parts: one is Excel as a host
| calling the scripts inside Excel, and the other is controlling
| Excel in another hosting language. The first can be done with
| VBA or Excel Javascript. The second can be done via the COM
| Automation interface supported by Excel. Any language that can
| talk to a COM Automation server can start Excel as a embedded
| COM object and call Excel's API to control it. Visual Basic,
| C++, C#, Java, JScript (Windows' Javascript), Mathlab, Python,
| and others can talk to COM Automation servers.
| aarondia wrote:
| One example is https://www.pyxll.com/ which lets you write
| Excel Add-ins using Python.
|
| I've never used it myself, but I have tried to use the Excel
| JS API, and it was quite a pain.
| ww520 wrote:
| Yes. I believe the way Excel hosting other languages inside
| it as scripting addon is via the COM Automation Server
| interface again. The other language needs to implement as a
| COM Automaton Server. Excel then instantiates the other
| language's Automation Server as an embedded COM object and
| calls its interface, which can call the functions
| implemented with the language.
| [deleted]
| Andy_G11 wrote:
| Excel is extremely accessible, which is both a strength and a
| weakness. People who are complete noobs are quite happy to open
| up Excel and pull together some logic and data with very little
| regard for the constraints of the tool. Then they can save it and
| email it to you and you can work with it. It is the epitomy of
| 'move fast and break things'. Try getting them to pull together a
| quick JavaScript file to show you what they are thinking...
| Whichever way Excel develops, I hope that it doesn't put off the
| noobs from their penchant to have a go because as much as there
| is the possibility for the tool to go horribly wrong, elevating
| it out of reach of the layman would be worse.
| inopinatus wrote:
| This is the way the world ends Not with a bang but a
| #VALUE! -- T.S. Eliot, The Hollow Men (1925)
| dang wrote:
| Some related threads:
|
| _Lambda: The Ultimate Excel Worksheet Function_ -
| https://news.ycombinator.com/item?id=26900419 - April 2021 (109
| comments)
|
| _Xkcd: Excel Lambda_ -
| https://news.ycombinator.com/item?id=26899793 - April 2021 (1
| comment)
|
| _Lambda: The Excel Worksheet Function_ -
| https://news.ycombinator.com/item?id=25990978 - Feb 2021 (1
| comment)
|
| _Lambda: The ultimate Excel worksheet function_ -
| https://news.ycombinator.com/item?id=25923628 - Jan 2021 (4
| comments)
|
| _Lambda: Turn Excel formulas into custom functions_ -
| https://news.ycombinator.com/item?id=25318386 - Dec 2020 (135
| comments)
|
| _Announcing LAMBDA: Turn Excel formulas into custom functions_ -
| https://news.ycombinator.com/item?id=25312725 - Dec 2020 (1
| comment)
|
| _Microsoft introduces LAMBDA functions for Excel_ -
| https://news.ycombinator.com/item?id=25295120 - Dec 2020 (7
| comments)
| deepstack wrote:
| In some way sql programming like functions, cursor, store
| procedures is the Advance Excel programming. Why reinvent the
| wheel?
|
| In regards to having JS/Python scripting it, if you using
| Postgres, just add v8 or python plugin, and there you have your
| advance excel programming in the language of your choice.
| makemexx wrote:
| very interesting. By the way, sometimes for study and work I use
| https://audext.com/ to transcribe podcasts.
| kamocyc wrote:
| Related:
|
| Innovation by (and beyond) the numbers: A history of research
| collaborations in Excel
|
| https://www.microsoft.com/en-us/research/blog/innovation-by-...
| aperrien wrote:
| Are there any plans to add Let/Lambda functionality to
| LibreOffice/OpenOffice?
___________________________________________________________________
(page generated 2021-05-05 23:02 UTC)