6 ms·

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

• griotspeak 13y ago
  This is a personal favorite - http://createdigitalmusic.com/2012/01/spreadsheet-as-music-tracker-sequencer-with-libreoffice-nee-openoffice/ http://createdigitalmusic.com/2012/01/spreadsheet-as-music-t...
  • vgrichina 13y ago
    This is nice – I visualised something like this in my head, but never knew somebody already implemented it :)
• patio11 13y ago
  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 13y ago
    "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.
  • vgrichina 13y ago
    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 13y ago
      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."
• vijucat 13y ago
  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 13y ago
    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 13y ago
      > I understand relation to FP. I even think of having lazy-evaluated infinite sequences in my system :) Nice!
• Searching11 13y ago
  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 13y ago
    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?
• jmg48 13y ago
  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 13y ago
    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.