All posts by kalx

xll_inet

This is still a work in progress, but I’ve been eating my own dog food and liking it: Win Inet for Excel. Let me know if you find something that breaks and open an issue on GitHub so I can fix it.

This add-in lets you grab a URL and put the characters returned in memory. Parse them as XML or HTML using the battle-tested libxml2 library used by etree in Python. Extract anything you want by manually traversing nodes or use XPath to zero in on exactly what you are after. It also has functions for parsing CSV and JSON into an Excel range.

JSON maps quite neatly to Excel. A JSON object is a two row range with keys in the first row and values in the second. A JSON array is a one row range of values. JSON primitive types number, string, boolean, and null correspond to Excel types xltypeNum, xltypeStr, xltypeBool, and xltypeErr with val.err = xlerrNull. Excel needs a little help to play nice with ranges that are recursive. You can use HLOOKUP to return a primitive type using the key and returning the second row but if the value is an object or array you will get a #VALUE!. Use JSON.VALUE(val, key) instead. The key can be an array of keys to recurse through or a jq-like dotted index. JSON arrays are indexed by 0-based numbers. Unlike jq, they should not be enclosed in square brackets.

xllmonte.com

There are plenty of products that do Monte Carlo simulation in Excel. Here is one more: https://xllmonte.com. Do an A/B test if you are already using one and let me know if theirs is simpler, faster, or easier to use. My product does not have many bells and whistles, or even examples of how to use it other than a simple demo showing how to trade space for time in a spreadsheet. It is a stripped down version of what I’ve been using with my hedge fund clients over the years.

They love using it and told me I should spin off a commercial version. They have no idea how difficult it is to write software that works on versions of Excel in the wild. If you help me shake down the issues involved with this I would love to keep rolling out new functionality. What problem can it help you solve?

xlladdins.com

If you are an Excel power user please have a look at https://xlladdins.com. It is still in early stages so any feedback will be appreciated. Currently there are add-ins for the C/C++ math library and Windows registry. The add-ins are signed but until I scrape up enough nickels for an EV (“Expensive Validation”) CA you will still see warnings when opening them in Excel. Who says money can’t buy you friends?

If that makes you nervous then you can build everything from source for yourself using the free version of Visual Studio. It should be as simple as cloning the repository in VS and hitting F5. Let me know if that does not work for you.

The xll_math library uses a python script to scrape the documentation from web pages and generate C++ code. I work hard to be lazy. The xll_registry library scratches an itch that I am surprised nobody else has scratched. Even VBA does not come equipped with this functionality.

I Work Hard to be Lazy

I endeavor to be lazy so I work hard to figure out something difficult if that means I can be lazy ever after. One lesson I learned from Mark Joshi (of xlw fame) is that people give up on using your software unless it is dead simple. It took a lot of work to figure out a way to make xll easy use.

Standard .msi Microsoft install files have become problematic, aside from the problem of creating them in the first place. If they are not signed by a Certificate Authority users get ominous warnings. After buying up all the CA companies, Microsoft figured out a way to extract even more money from software developers by inventing Extended Validation certificates that you can write a check for to make those warnings go away when using their products.

Visual Studio can use VSIX or NuGet to install packages without requiring certificates. They are almost as much fun as creating a .msi file. We now have MSIX and winget if you enjoy package management insanity.

There is an easier way.

Building software involves wrangling files that change over time. One might think that this should be a solved problem by now, but we live in a world where lesser minds have become fascinated by looking for ponies in Linus’ pile of git. He built git to solve his problem: how to merge (lots and lots of) changes into the Linux kernel code. Submodules were an afterthought and his original advice on how to implement those seems to be this.

The current version of git used by GitHub implements submodules adhering to his advice. Instead of a fancy pants package management system, you can just clone xllproject1 and it will drag in all the xll files you need to get started. Those files never change behind your back as you write new code. Visual Studio does not grok submodules, yet, so you need to start a Command Prompt (Tools > Command Line > Developer Command Prompt) and cd xll; git pull to get the latest version. Not dead simple, but the version of xll to use is under your control.

LET’s LAMBDA!

So Microsoft is rolling out LET and LAMBDA. Using The Wisdom of the Ancients (also known as the Excel SDK) something similar can be done using good, old fashioned, xlfEvaluate. The function XLL.EVAL takes any Excel formula as its first argument and evaluates it using the remaining arguments. It uses _1, _2, ... in the formula to refer to the arguments. If you want named arguments use key-value pairs like {foo, 1.23}. These can be mixed and matched at your pleasure.

Their Thanksgiving example would be XLL.EVAL(Thanksgiving, {year, 2020}) where Thanksgiving is the string "TEXT(DATE(year, 11, CHOOSE(WEEKDAY(DATE(year, 11, 1)), 26, 25, 24, 23, 22, 28, 27)), """mm/dd/yyyy""")", although "DATE(year,11,MOD(12-WEEKDAY(DATE(year,11,1)),7)+22)" seems simpler.

One thing to keep in mind is that references and defined names occurring in the first argument must be preceded with an exclamation point. Don’t ask me why because I don’t know. Excel is Excel.


It might be big, but it sure is slow

I’ve been getting up to speed with the latest in JavaScript add-ins for Excel. I ran the generator to create a sample add-in and took a look at what was generated:

WTF? 25,487 files weighing in at 147MB! WTF-ingF? That is insane.
Fine, it builds and runs, if you have enough patience. The functions even return results that appear to be correct.
Lets add the getStarCount example and rebuild. Works as advertised. Now let’s copy and paste into, say, 100 cells. After several seconds of #BUSY! maybe 10 of the calls succeed and the rest come back with #VALUE!. F9 does nothing. Shift-F9 does nothing. Ctrl-Alt-F9 does nothing. Alt-replace-equal-by-equal-all, nada. F2 an individual cell maybe? Nope.

Don’t get me started with how add-ins are now distributed. You might as well wear a straitjacket and leg irons.

xllmath

I’ve been eating my own dogfood and it sure is yummy! The xllmath add-in hooks up functions in the C++ standard <cmath> runtime library to Excel. I work very hard to be lazy so I wrote some python code to generate the add-in from the GitHub documentation provided by Microsoft. Thank you Satya!
It is just a first cut but it compiles and runs. Try out my kibbles ‘n bits and let me know what you think.

New xll add-in library

I’ve rewritten my library for creating Excel add-ins for the n-th time: xlladdins.com. Let me know what you think.

The biggest change is that it now uses UTF-8 so you don’t have to deal with multi-byte (ASCII) character sets vs Unicode (UTF-16, UCS-2) pain. Everything gets converted to whatever makes Excel happy. Hopefully that makes you happy too.

If you have issues please post them to GitHub Issues. It’s not that I prefer the evil, money grubbing, GitHub capitalists to the evil, money grubbing, WordPress capitalists, I just like having things in one spot and I’m on GitHub more regularly.

Disclaimer: This is at the Minimum Viable Product stage so please don’t use it in production unless you need to get fired.

Msiery

I have been working on a new release of my add-in for doing Monte Carlo simulation (and mucho more!) in Excel. You can help me by trying out xllMonte . I am still shaking down the current release so expect to run into problems. The good news is I have the release procedure mostly automated so I can get you a quick turnaround on anything that comes up.

The install process is still too complicated. You have to download and unzip both the setup.exe and xllmonte.msi files and run setup as administrator. I am using the (free)  Visual Studio Installer Project extension to create the msi file.

I’ve used this in the past and it worked fine for what I needed. Then there was a period when Microsoft wanted you to use InstallShield Lobotomiz^WLimited Edition until you find out it was pretty useless and had to pay for the Actually Useful version.

The next thing I tried was WiX. No, not that WiX, this WiX. It was a bit daunting at first, but the more I looked into it, the more impressed I was by their product. These are top-notch software engineers providing high quality tools to solve complicated problems.

The Windows Installer is a big, complicated machine and I don’t claim any expertise in all of its ins-and-outs, but a msi file is just a database. You can use Orca to see and edit (don’t try this at home kids) the tables. The tables declare what state(s) you want to achieve –  e.g., where files get installed, what registry entries you need – and msiexec reads the database and executes the state machine to make that happen.

As I’ve painfully found out, the Visual Studio Installer Project does not give you full access to the Orca database. It gets worse – in Windows 10 the msiexec program runs with very low privilege. So low that you can’t even modify HKCU registry entries!

I use a Certificate Authority to sign the add-in and installer but still get the nasty warnings about installing “dangerous” software. It turns out there is a new CA in town: Extended Validation Certificates.  Money can’t buy you love, but it can buy you trust. At least from Microsoft.

For now you can run setup.exe as administrator, which then runs xllmonte.msi with appropriate privileges, and then you can get up and running.

I am not worried one iota about AI putting programmers out of work. There are an infinite number of fiddly little details that have to be solved to produce a product human beings want to use. Unless, perhaps, AI can train people to be satisfied with crappy products. We seem to be starting down that road…

Of course I’m probably missing a simpler solution. Maybe Windows Store is the right way to go.

 

Simpler Unit Test Framework

The C++ Range library always seemed overly complicated to me. I’ve been working on https://github.com/keithalewis/sequence lately. I work hard to be lazy and came up with an easy way to add unit tests: just write a function returning an `int` and call it to initialize a global `int` variable. The driver is just a translation unit having an `int main() { return 0; }`

See https://github.com/keithalewis/sequence/blob/master/binop.t.cpp for an example.