I'm thinking on making my own "spreadsheety" system for software development – http://spreadsheets-on-steroids.tumblr.com<p>I'd like to hear some interesting unusual ways you use spreadsheets. Especially some use cases for which Excel seems like a total kludge but manipulating visible data instead of symbols still helps.
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.<p>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.
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.
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.<p>When you use Excel's "Trace Dependents" functionality, you're basically asking for a Call Graph!<p>In this sense, a spreadsheet is a tangible functional programming system. @see Conal Elliot's Eros : <a href="http://conal.net/papers/Eros/eros.pdf" rel="nofollow">http://conal.net/papers/Eros/eros.pdf</a><p>I get the feeling there is a GREAT idea in this space waiting to happen, so by all means, go ahead and rock it! :-)
This is a great concept, and in fact already exists as an Excel addin - <a href="http://schematiq.htilabs.com" rel="nofollow">http://schematiq.htilabs.com</a><p>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.<p>There is also a Q&A forum at <a href="http://schematiq.htilabs.com/community" rel="nofollow">http://schematiq.htilabs.com/community</a> so if you need to ask any questions you should be able to get some help.
This is a personal favorite -
<a href="http://createdigitalmusic.com/2012/01/spreadsheet-as-music-tracker-sequencer-with-libreoffice-nee-openoffice/" rel="nofollow">http://createdigitalmusic.com/2012/01/spreadsheet-as-music-t...</a>