

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

I&#x27;m thinking on making my own &quot;spreadsheety&quot; system for software development – http:&#x2F;&#x2F;spreadsheets-on-steroids.tumblr.com<p>I&#x27;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.
======
patio11
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.

~~~
vgrichina
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?

~~~
patio11
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."

------
Searching11
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.

~~~
vgrichina
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?

------
vijucat
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](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! :-)

~~~
vgrichina
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.

~~~
vijucat
> I understand relation to FP. I even think of having lazy-evaluated infinite
> sequences in my system :)

Nice!

------
jmg48
This is a great concept, and in fact already exists as an Excel addin -
[http://schematiq.htilabs.com](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](http://schematiq.htilabs.com/community)
so if you need to ask any questions you should be able to get some help.

~~~
vgrichina
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.

------
griotspeak
This is a personal favorite -
[http://createdigitalmusic.com/2012/01/spreadsheet-as-
music-t...](http://createdigitalmusic.com/2012/01/spreadsheet-as-music-
tracker-sequencer-with-libreoffice-nee-openoffice/)

~~~
vgrichina
This is nice – I visualised something like this in my head, but never knew
somebody already implemented it :)

