Spreadsheets as functions

Some significant time ago those nice people at Resolver emailed me about a new feature they had added to the then latest version. (BTW they have some competition on at the moment to win 17k USD – thats about half a million fine British pounds I think at today’s exchange rate?)

Unfortunately, it arrived at the point of maximum turmoil in my web life (change of hosting and all the crap that goes with)

Anyway the point was a new feature that allows one resolver workbook to expose its logic as if it were a function to other workbooks. That means you can pass in parameters and receive back an answer/value.

I think that’s pretty neat. I have always maintained that the most important component of spreadsheets is the logic not the data. The data is normally sourced from, and still available in, some enterprise system somewhere. People aren’t trying to expose the static data they have in spreadsheets – they are trying to expose the logic, certainly in my experience. Would you agree?

I’ve written a few systems that open up a specific workbook in Excel throw in a load of values, calc, run some VBA (sorry Excel services!) calc a bit more, then fire out a value or a few values to some web front end or other spreadsheet. I think that is a fairly common usage pattern, does anyone else come across it?

What sort of things do you do to implement this functionality?

Do you use Resolver to do it?

cheers

Simon

Advertisements

7 Responses to “Spreadsheets as functions”

  1. John Walkenbach Says:

    I’ve never used or done anything like that, but it reminds of of a 5-year old paper by Simon Peyton Jones: A User-Centred Approach to Functions in Excel.

    http://research.microsoft.com/en-us/um/people/simonpj/Papers/excel/excel.pdf

  2. Simon Says:

    I think they patented some part(s) of that too.

  3. dougaj4 Says:

    That paper was an interesting read, but I’m not conviced that the proposed UDF on a worksheet model is actually any more accessible to the average user than the VBA UDF model.

    From my perspective (engineering at the coal face) the reasons people don’t use UDFs as much as they might are:

    1) Too busy to muck around with that stuff
    2) Prefer to stick with what they know and what works
    3) If they really need a UDF they’ll hand it to the “office office expert”
    4) Lack of good “get you started” help packaged with the program, and surfeit of poorly-structured help on the Web.

    I don’t think that lack of familiarity with programming is a big issue, at least in engineering. They do that at uni, and if they are into that stuff will continue to do it afterwards. If they aren’t, they aren’t going to do UDFs in a worksheet either.

  4. Giles Says:

    Simon – thanks for the mention!

    dougaj4 – you’re right, how-to documentation is lacking for how to build UDFs using any spreadsheet. We’ve tried to do a better job ourselves with a load of screencasts (the one for the worksheets-as-functions stuff is here: http://www.resolversystems.com/screencasts/runworkbook/) and it will be interesting to see whether that helps with uptake among non-developers…

  5. Mike Staunton Says:

    The MS article is just so horrible and impractical – I prefer to teach VBA as learning by doing – here is my call function, albeit with my own function vaCND for the cumulative normal distribution because the Excel one is not accurate enough

    Function vaEuroCall(S#, K#, r#, q#, Tyr#, vol#) As Double
    Dim d2#
    d2 = (Log(S / K) + (r – q – 0.5 * vol * vol) * Tyr) / (vol * Sqr(Tyr))
    vaEuroCall = S * Exp(-q * Tyr) * vaCND(d2 + vol * Sqr(Tyr)) – K * Exp(-r * Tyr) * vaCND(d2)
    End Function

  6. Harlan Grove Says:

    I haven’t tried it, but the idea of using a spreadsheet as a udf seems suboptimal. Yes, you may be able to do interesting things with them, but in Excel terms, this isn’t much different than using Data Tables. Also, the Black-Scholes udf could be implemented with two single-expression udfs. The portfolio value udf could be replaced with a single, moderately complicated array function.

    That said, I do like Resolver’s referencing syntax. Excel’s structured references into Tables is a baby step in the right direction. If only structured references included record selection syntax, e.g.,

    SUM(A1:H20[Field1=”XYZ”][Field3])

    as a functional equivalent for SUMIF(A1:A20,”XYZ”,C1:C30). Better still if Field3 could be an expression, i.e., something equivalent to the n term in

    SUMIF(A1:A20,”XYZ”,INDEX(A1:H30,0,n))

    But that begs the question of how to implement OR criteria. Then again, simply making SQL.REQUEST a built-in function would solve LOTS of formula problems.

  7. Giles Says:

    Harlan – glad you like Resolver One’s referencing syntax; here’s how you could use it to do something like SUMIF with conditions over two columns in a three-column range:

    =SUM(c for a, b, c in D1:F3.Rows if a == True and b.endswith(“foo”))

    You’re right that Black-Scholes is probably a smaller problem than you’d normally want to solve using RunWorkbook – we chose it as a small enough example that people could understand in a short screencast. Our aim isn’t really to replace code-based UDFs, but instead to help people who’d never dream of writing code to produce stuff that can be reused by other spreadsheet users.

    Cheers,

    Giles

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: