Podcast

My good friend and colleague CSJ and I started recording some of our conversations, mostly as an experiment to see how hard podcasting is.  We decided to actually start publishing material as a podcast.  Right now no one else has listened to it but who knows, one day there may be literally dozens of listeners.

2dt.fm

Podcast

Merged Cells and Formatted Text in SpreadsheetApp

Google Apps Script is a really nice way to interact with Google Docs programatically.  You can write add-ons or stand-alone scripts to read or modify documents and have access to a DOM, which is a much nicer way to do things than attempting to use a REST endpoint or similar. The only problem is that while the DOM/object model is reasonably complete, it isn’t totally so.  As of this writing there is no way, when dealing with spreadsheets, to determine if a cell is merged, or, more frustratingly, obtain the formatted contents of the cell.  We really needed this information recently, so we decided to parse it out of the HTML that you get when exporting the document.  This can be done in Apps Script itself.

You can see the gist here.

Basically we build up an associative array, where for a given cell index you can get back the row span, column span and formatted value.


parsedHtml['Sheet1:A1'] = {index: 'Sheet1:A1', text: '123,456', rowspan: 2, colspan: 2}

If there is no entry for a given cell index then that cell is part of a merged range or outside the data range.  The latter should be known beforehand.

NOTE: that this code requires underscore.js to work, you can read an article from Google about how to use it in apps script projects here.

Merged Cells and Formatted Text in SpreadsheetApp

View Adobe Swatch Exchange Files Online

The other day I was trying to do some web design according to IBM’s design guidelines.  They provide color swatches as .ase files, which are read by Adobe products like Illustrator, PhotoShop, etc.  I have none of these applications but I figured “hey how hard can it be to parse the file myself?”

Two days and a fair bit of hair tearing later witness palettes.mybluemix.net!

I later found out that IBM Design does provide a site that shows the same information, but by that time I was way to consumed with the problem.  That happens sometimes.  But hey, now I can look at any .ase file!  And so can you!

View Adobe Swatch Exchange Files Online

Why Does Apple’s Revenue Spike Every Fourth Quarter?

http://xbrl.mybluemix.net/show/b4fe1406-41f8-4cb7-a22a-efd1c86b4195

Could be again that I’m just not displaying the data the right way.  Or, that the fourth quarter is more important from an analyst perspective so Apple will try and “close” more revenue in Q4?

These are the things I find confusing about accounting, though it’s interesting to learn the reasons why things are the way they are.

Why Does Apple’s Revenue Spike Every Fourth Quarter?

Substitution Ciphers – Not Just for the Ancient Roman Military

Recall from a previous post that we were trying to get multiple “distinct” fields from a Cloudant search index.  We did this by concatenating the two fields during index creation.  Recall also that there was serious drawback with this method, that we were indexing both fields, rather than indexing one and getting both back.

This isn’t a problem if the two fields are using strictly different characters spaces.  For example, one only alphabetical characters and one only numeric characters.  Even if this isn’t the case, we can force it to be using a substitution cipher.  JSON documents are not limited to ASCII characters, the UTF-8 character space is quite large, and we can simply shift one of the fields to a totally different character set when creating the index.

Here’s how the search index would look (credit to Tim Severien’s cipher example on GitHub for the rotate function):

function rotateText(text, rotation) {
    // Surrogate pair limit
    var bound = 0x10000;

    // Force the rotation an integer and within bounds, just to be safe
    rotation = parseInt(rotation) % bound;

    // Might as well return the text if there's no change
    if(rotation === 0) return text;

    // Create string from character codes
    return String.fromCharCode.apply(null,
        // Turn string to character codes
        text.split('').map(function(v) {
            // Return current character code + rotation
            return (v.charCodeAt() + rotation + bound) % bound;
        })
    );
}

function(doc){
	index("companyName", rotateText(doc.identifier, 500) + ' ' + doc.name, {"store": true, "facet":true});
}

We’re creating the index on the concatenated string consisting of the cipher-ed identifier and the original name. This means the pair [‘0000051143’, ‘INTERNATIONAL BUSINESS MACHINES CORP’] will become the string “ȤȤȤȤȤȩȥȥȨȧ INTERNATIONAL BUSINESS MACHINES CORP”.

From here we continue as before, using faceting to return a list of distinct results. It’s then simply a matter of reversing the cipher to get the identifier back (in this example rotate the text by -500). You can either make the very reasonable assumption that no one will enter characters from the cipher-ed character space into your search field, or not allow searches that contain them.

Substitution Ciphers – Not Just for the Ancient Roman Military

Financial Concept Text Cloud

I’ve been playing with the IBM RAVE engine a bit today. I tried applying it to the data at xbrl.mybluemix.net. Here’s a text cloud showing the 100 most reported financial concepts. What does this mean? Nearly all filers are likely to report certain concepts like Assets. Furthermore, a concept that is more highly dimensionalized has more data points.  For example, here’s General Motor’s breakout of StockholdersEquityIncludingPortionAttributableToNoncontrollingInterest.  Very highly dimensionalized.

conceptCloud

Unfortunately the query to generate this text cloud takes about 40 seconds to run, so it’s not feasible to have it live.

Financial Concept Text Cloud