Ask HN: What are the crazy/clever ways you use and abuse spreadsheets?

I couldn't figure out a way to import transactions sanely into Yayoi Kaikei (the Quickbooks of Japan) to satisfy Japanese requirements for double-entry bookkeeping, so I implemented double-entry bookkeeping up to Japanese GAAP with about a dozen spreadsheets. It had to reconcile with information I got from a half-dozen systems, in varying formats. Also, most of the information coming in was in dollars, English, and either PST or GMT -- all three of which would get a look of disapproval from the Ogaki tax office -- so there's extensive use of historical lookup tables to massage the data to meet requirements without having to pay a bilingual bookkeeper to manually process everything.

If you add in the one-and-done Ruby scripts that I made to grab the information from the various computer systems and then spit out CSV for copy/pasting into Excel, this is unquestionably the worst bubble gum and duct tape programming job I've ever had the misfortune of participating in -- but it let me submit my tax return in time for the deadline, with numbers that looked pretty reasonable.

As you embark on this project, I'd like to point out the relationship between spreadsheets and Functional Reactive Programming (FRP) : each cell with a formula is what one could call purely functional (referentially transparent) in FP, and their chaining together is basically functions depending on other functions.

When you use Excel's "Trace Dependents" functionality, you're basically asking for a Call Graph!

In this sense, a spreadsheet is a tangible functional programming system. @see Conal Elliot's Eros : http://conal.net/papers/Eros/eros.pdf

I get the feeling there is a GREAT idea in this space waiting to happen, so by all means, go ahead and rock it! :-)

When working for a bank I was asked to maintain an ongoing list of approximately 1,500 prospective M&A targets, I did so in excel. I stored basic information about the M&A targets (approx. revenue, # of employees, etc.) as well as CRM type information (last contacted, relationship owner,etc). I then created a two documents that could be generated based on a unique identifier I had created for each M&A target. The first document was a "one slider" on the prospect. A user could enter the unique identifier of a prospect and what was returned was something that looked like a one slide summary on the prospect created in PowerPoint. I formed the "one slider" by re-sizing all the cells on the spreadsheet to 2 by 2, for design flexibility, and using a combination of vlookups and hlookups to retrieve data. Similarly, the other document produced was a questionnaire that an executive could print out before a meeting with an M&A target so that he/she knew what information we already had on the target and what information we still needed.

This is a great concept, and in fact already exists as an Excel addin - http://schematiq.htilabs.com

It's paid-for software, but available free for personal use. Put simply, it extends the concept of what can be stored within a single cell on the spreadsheet, enabling much more powerful functionality.

There is also a Q&A forum at http://schematiq.htilabs.com/community so if you need to ask any questions you should be able to get some help.

This is a personal favorite - http://createdigitalmusic.com/2012/01/spreadsheet-as-music-t...