The Excel Gambit


Excel screen shot of XL-Gambit
XLREPL


In the spirt of shoving Lisp where Lisp should-not-go I have jammed Gambit Scheme into Excel.

If you’re interested in trying it out you should go and get the files RIGHT NOW at GitHub. You just need to add the compiled .XLL into your add-ins and use the formula =GAMBITC(“(+ 1 1)”) in a cell of your choosing.

Please note this is a proof-of-concept. You probably shouldn’t use it for anything important, most importantly because it probably leaks like a sieve. You should also know I only really tested it on Excel 2003. It should load into Excel 2007 but I didn’t try that yet.

Why? Because it’s there, and I’m here

Why did I do it? Well the rationale behind doing this was that I wanted to write an app for Excel in Scheme. I want to do that because I think it would be vastly superior to using .NET or VBA or COM. Having spent a few hours on it I think I could definitely achieve what I set out to do but whether I’ll actually go this route in the end I’m undecided about. More on that later.

Mature like a good cheese

The Excel API, as it turns out, is … well … mature. Now I like old things quite a lot. I’m old, and I like me just fine. One consequence of age is that we get a little extra baggage on the way. This is true of Excel too.

Now, IMHO one of the more challenging aspects of programming a long-lived project is version management. Especially version management of an API. Broken backwards/forwards compatibility of those APIs could be serious impediments to a new Excel’s acceptance. Unsurprisingly then Excel has a number of APIs that have all undergone various enhancements over the years. To me though the switch from Excel 2003 to Excel 2007 was the most significant, requiring the most additional cognitive load to work with. For now, and for simplicity, I have chosen to totally deny the existence of Excel 2007. But I’m coming back to it, oh yes I am.

I Eat Pain For Breakfast

I had read a bit online about the Excel C API and found it all hugely confusing. So I bought a book thinking that that would make it clearer. Although the author does attempt to explain how you might write an Excel add-in he does it in a way that makes programming from it hard. For example, the information I need is spread all-over the book. Answering a single question about Excel’s behaviour with respect to calling the core Excel4/Excel12 function necessitates flicking between the index and more than 10 different page sections.

The reason for this is probably that Excel is really a very versatile tool which is why a lot of the world’s business (large and small :)) seem to operate their database, accounts and customer details from it. So there’s not really one track through the development process. There’s literally hundreds, limited only by your imagination!

Having said that the book in combination with the Excel 2007 SDK was sufficient to pick through the rubble and build something workable. Maybe I’ll try and produce some guides/tutorials of my own to make the topic clearer. Maybe.

On the Gambit side there’s not much more documentation than what’s on the manual HTML page to help. Like the Excel book it is also very densely populated with information. IMHO its main failing is that it could really do with having more documentation of the C macro functions. Did I ever mention that I hate C macros? Well in Gambit these undocumented C macros are effectively what Gambit Scheme is written in and you kind-of need a fairly thorough explanation of how it all fits together to be able to make an effective glue with them. I will be coming back to this later when I try and construct lists from cell ranges.

Finally I decided to do this all in MinGW rather than the more usual VC++. Whilst going this route did cost me a bit of time I’d much rather use the GCC toolchain because I understand it better.

Where-to-now

Now that I have the interpreter in Excel I will probably work on tidying it up for Excel 2007. More importantly though, I want to see if I can exploit Excel 2007’s all important multi-threading capability.

If that all works out I might use it to make a custom add-in. I will develop the app from the REPL in Emacs using Gambit Scheme. When it’s done I’ll compile it up into a standalone XLL with Gambit. That’s the plan anyway.

Let me know if you like it, or can see a use for it. Encouragement is always good.

Pythagoras Would Puke

This week a colleague pointed out this comment from an Ask Reddit:

danceswithwolves 62 points 4 days ago* [-]
I went to a job interview and the interviewer told me I would get the job (my first full time job ever) if I could answer this math question which is supposedly a middle school grade question. I had no clue how to solve it so I’m jobless now and suffering. To this day I never figured out the answer. http://img691.imageshack.us/img691/2192/mathv.png (The lines are curved if that makes a difference)

Basically the problem asks you to find the area of a sort-of-square, inside a square of side 4. Each corner of the outer-square is joined to the adjacent one by an arc of angle pi/2 radians (90 degrees) of radius 4. The sort-of-inner-square is outlined by the four crossing arcs. See the link above, or the picture below for a diagram.

Normally when faced with puzzles of this sort I try and pretend I’m too busy to figure them out. Partly because I’m belligerent, bordering rude, and partly because I know I’m a bit dumb. If my life depended on getting the answer I’d be dead already. So it was a strange thing indeed that I found myself working on and actually enjoying this particular puzzle. I did, of course, out of all of my colleagues get to the answer last.

It then occurred to me that I could regain some cool by trying to draw a better diagram (than the one in the comment) using the Common Lisp vector drawing package Vecto. It was actually a lot easier than I thought it was going to be and came out quite nice, the result is here: Area of shape the code is here.

After arriving at our respective numerical answers some bright-spark said that it should be possible to verify our results using Monte-Carlo methods. I thought about this for a bit and then concluded that it would actually be quite easy to do, especially in Lisp where functions are first class objects. You can see the code here.

As fun as it was to find the answer by Monte-Carlo it was a bit of a waste of time since I could have just as easily subdivided the area into a grid of tiny squares and then counted the ones that were inside and outside the shape. This would have been simpler to code and it would have been more accurate too.

Pythagoras would probably have known that of course.

Mersenne Twister in Clojure

The Mersenne Twiser is a random number generator that has a lot of applications, particularly in finance. After discovering that there was no Clojure implementation at Wikipedia I decided to give it a try as my first attempt at something useful in Clojure. As it turns out it’s problably not a good candidate to be implemented in a functional language because the whole thing requires modifying a mutable array for every call to (genrand).

I’m not very excited by the solution because it doesn’t seem very lispy. It much more resembles the reference implementation, and gives the same results as it for all the tests I tried.

I’m sure it could be better though.

If it’s of any use to anyone you can find it here.

REPL to the rescue!

Yesterday I watched Dan Weinreb talking about ITA, Lisp, and well, really, really complex stuff 😉

The kind-of conclusion that he drew on the future of Lisp is that Clojure is the next Common Lisp. I’ve been dodging Clojure for about a year now. I bought the book from The Pragmatic Programmers when the book was in beta and eagerly followed the examples and decided it had promise. But, I figured that if I needed to really learn it (and by that I mean use it, not just talk about using it) it would have to come to me. It would do this, I reasoned, by being hard-to-ignore.

Well there seem to be more than enough smart-folks on-board now (like: Eric Normand, Bill Clementson to name only two) so I guess I better not miss the party. Not because I’m smart but because if I don’t make too much noise I can blend in and no one will notice I’m there.

So I came to the conclusion that I should try and use it for ad-hoc things that might cross-my-dome and are hard(er) to solve in non-functional languages.

For instance, this very afternoon I wanted to know how many possible hand distributions by suit there are in the card game Bridge. So four possible distributions might be:

  • All 13 hearts
  • 12 hearts, one diamond
  • 11 hearts, one diamond, one club
  • 10 hearts, one diamond, one club, one spade

The question is how many total distributions are possible?

I banged my head on the table-hard trying to figure out the answer to this. At first it seemed like a simple counting problem, but if it is I’m too simple to see it. Then I wondered if it could be an additive patitioning problem, but ordering is important so I don’t think it is. It didn’t feel NP complete. I know one thing though, at this late hour it might as well be.

1:57 bridge=> (count (for [spades (range 0 14) 
                                hearts (range 0 14) 
                                diamonds (range 0 14) 
                                clubs (range 0 14) 
                                :when (= 13 (+ spades hearts diamonds clubs))] 
                                [spades hearts diamonds clubs]))
560

Functional programming rocks.

cl-mysql on github

I’ve seen the future and it is git. Some believe Mercurial is the way because it is more accessible, but I’m not so sure.

We’re programmers, we love this esoteric shit. The more hardcore the better. IMHO, the force of numbers, and its pedigree, will probably make the big fat git wade through the opposition like the monster it is.

Anyway, for a few months now I’ve been seeing this github thing linked here and there, I figured it was about time I checked it out.

I went, I saw, and it was good. Fast, easy-to-use and beautifully in the spirit of free-software (you only have to pay to use it for private repositories).

So in the spirit of free lovecode I’ve added cl-mysql.