News for hackers. Stuff that matters.

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

I'm thinking on making my own "spreadsheety" system for software development – http://spreadsheets-on-steroids.tumblr.com

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.

( 13 comments | 6 points | on HN )
Re:Ask HN: What are the crazy/clever ways you use and abuse ... by patio11 on Friday March 07, 2014 @08:49PM (#7363903)
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.

Re:Ask HN: What are the crazy/clever ways you use and abuse ... by vgrichina on Saturday March 08, 2014 @06:12AM (#7364894)
"bubble gum and duct tape programming job" – I guess you just found a name for the programming paradigm I plan to implement :)

As I understood in your case a system was a mix of what spreadsheets are good fit in (accounting) and where it is currently a kludge (gathering and massaging data).

So I think I'll explore data massaging in more depth. I am a bit concerned however with the representation of hierarchical data (like JSON) in spreadsheet model.

Re:Ask HN: What are the crazy/clever ways you use and abuse ... by vgrichina on Saturday March 08, 2014 @07:47PM (#7367011)
BTW, what were the main difficulties/problems in implementing your project using spreadsheets?

I assume that one of these was having to write Ruby scripts to connect things together instead of working directly with your data sources, was there anything else?

Re:Ask HN: What are the crazy/clever ways you use and abuse ... by patio11 on Sunday March 09, 2014 @12:24AM (#7367563)
I have never had the need to do anything particularly complicated in Excel before, and had to learn how to express things that would have been very easy for me in Ruby, like "Servers, domain names, telephone bills, Twilio credits, and the like count as 通信費. Sum the total of all 通信費 given a list of all of your expenses for the past year."
Re:Ask HN: What are the crazy/clever ways you use and abuse ... by Searching11 on Saturday March 08, 2014 @03:51PM (#7366355)
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.
Re:Ask HN: What are the crazy/clever ways you use and abuse ... by vgrichina on Saturday March 08, 2014 @07:45PM (#7367007)
So as I understood you wanted the ability to generate nice-looking report from your data?

So you had to resort to making extremely small cells to allow for precise positioning of elements, correct?

Re:Ask HN: What are the crazy/clever ways you use and abuse ... by vijucat on Saturday March 08, 2014 @05:17AM (#7364802)
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! :-)

Re:Ask HN: What are the crazy/clever ways you use and abuse ... by vgrichina on Saturday March 08, 2014 @06:16AM (#7364902)
I understand relation to FP. I even think of having lazy-evaluated infinite sequences in my system :)

BTW, great thanks for the paper, it seems to be closely related to what I'm working on.

Re:Ask HN: What are the crazy/clever ways you use and abuse ... by vijucat on Sunday March 09, 2014 @12:47AM (#7367620)
> I understand relation to FP. I even think of having lazy-evaluated infinite sequences in my system :)

Nice!

Re:Ask HN: What are the crazy/clever ways you use and abuse ... by jmg48 on Monday March 17, 2014 @08:44AM (#7414671)
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.

Re:Ask HN: What are the crazy/clever ways you use and abuse ... by vgrichina on Saturday March 29, 2014 @11:34AM (#7492546)
Thats interesting, thanks!

I plan to build something less limited to the traditional spreadsheet UI though. Same concept but more flexible UI, so that for example you may process image data by manipulating pixels as cells.

Re:Ask HN: What are the crazy/clever ways you use and abuse ... by griotspeak on Friday March 07, 2014 @08:32PM (#7363858)
Re:Ask HN: What are the crazy/clever ways you use and abuse ... by vgrichina on Saturday March 08, 2014 @05:42AM (#7364837)
This is nice – I visualised something like this in my head, but never knew somebody already implemented it :)