My main goal is to connect Tableau in an easy way to my company’s Asp.Net WebAPI site. We use JSON as our interchange format between server code (C#) and the client code (JavaScript and HTML). While that data is only available within my company network it is easy enough to find some JSON data sources on the web.

A friend introduced me to Quandl. They have load of different financial dataset readily available in a variety of different formats. For what I needed for developing the connection I chose to look at the USD/JPY FX rate history: https://www.quandl.com/CURRFX/USDJPY-Currency-Exchange-Rates-USD-vs-JPY. They provide this data in a variety of downloadable formats:

For what I wanted to do, I used the JSON format. The URL to the data is:

https://www.quandl.com/api/v1/datasets/CURRFX/USDJPY.json?trim_start=1991-03-05&trim_end=2014-10-31

There is documentation available on the API here https://www.quandl.com/help/api

Chrome has some great JSON tools available. I tend to use PostMan to make test JSON requests that I can then fiddle with:

Reading the dataset in within python is very straight forward:

def downloadJSONData(ticker, startDate, endDate):
      dataUrl = "https://www.quandl.com/api/v1/datasets/{0}.json?trim_start={1}&trim_end={2}".format(ticker, startDate.strftime("%Y-%m-%d"), endDate.strftime("%Y-%m-%d"))
     data = urllib2.urlopen(dataUrl).read()
     return json.loads(data)

The dataset I work with in the office tends to be an array of JSON objects out of an ASP.Net Web API service, so I chose to reshape the JSON data in python to be more like the data I wanted:

def dateFromString(dateYYYY_MM_dd):
    return datetime.date(int(dateYYYY_MM_dd[0:4]), int(dateYYYY_MM_dd[5:7]), int(dateYYYY_MM_dd[8:10]))

def extractArrayOfDictionaries(jsonObject):
    colNames = jsonObject["column_names"]
    data = []
    for dataRow in jsonObject["data"]:
        dataObj = {}
        for colii in range(len(colNames)):    
            if colNames[colii]=="Date":
                dataObj[colNames[colii]] = dateFromString(dataRow[colii])
            else:
                dataObj[colNames[colii]] = dataRow[colii]
        data.append(dataObj)
    return data

This remapping function will need be adjusted to match the response from an web service, but after running this process on the JSON response the data looks like:

[{u'Date': datetime.date(2014, 11, 14), u'Rate': 115.567, u'Low (est)': 114.27, u'High (est)': 116.88},
, {u'Date': datetime.date(2014, 11, 13), u'Rate': 115.43, u'Low (est)': 0.0, u'High (est)': 0.0},  … ]

As the data is now typed and uniform, I use this to create the table definition and then iterate over each of the dictionaries and add it to the tableau file:

def createTDETableDef(tdeFile, dict):
     tableDef = tde.TableDefinition()
     for col in dict.keys():
         typeval = type(dict[col])
         if typeval is datetime.date:
             tableDef.addColumn(col, tde.Type.DATE)
         elif typeval is datetime.datetime:
             tableDef.addColumn(col, tde.Type.DATETIME)
         elif typeval is datetime.timedelta:
             tableDef.addColumn(col, tde.Type.DURATION)
         elif typeval is bool:
             tableDef.addColumn(col, tde.Type.BOOLEAN)
         elif typeval is int:
             tableDef.addColumn(col, tde.Type.INTEGER)
         elif typeval is float:
             tableDef.addColumn(col, tde.Type.DOUBLE)
         else:
             tableDef.addColumn(col, tde.Type.UNICODE_STRING)
     return tableDef
 
def addRowToTable(tableDef, dict):
     row = tde.Row(tableDef)
     for ii in range(tableDef.getColumnCount()):
         val = dict[tableDef.getColumnName(ii)]
         type = tableDef.getColumnType(ii)
         if (type == tde.Type.UNICODE_STRING):
             row.setCharString(ii, str(val))
         elif (type == tde.Type.BOOLEAN):
             row.setInteger(ii, bool(val))
         elif (type == tde.Type.INTEGER):
             row.setInteger(ii, int(val))
         elif (type == tde.Type.DOUBLE):
             row.setDouble(ii, float(val))
         elif (type == tde.Type.DATE):
             row.setDate(ii, val.year, val.month, val.day)
         elif (type == tde.Type.DATETIME):
             row.setDateTime(ii, val.year, val.month, val.day, val.hour, val.minute, val.second, (val.microsecond // 100))
         elif (type == tde.Type.DURATION):
             row.setDuration(ii, val.days, val.seconds // 3600, (val.seconds // 60) % 60, val.seconds % 60, (val.microsecond // 100))
     return row
 
# Given a set of dictionaries create TDE
def createTDEFile(fileName, dicts):
     # Create TDE File, TableDef and Table
     tdeFile = tde.Extract(fileName)
     tableDef = createTDETableDef(tdeFile, dicts[0])
     table = tdeFile.addTable("Extract", tableDef)
     for dict in dicts:
         table.insert(addRowToTable(dict))
     tdeFile.close()
     return tdeFile

The whole process is now:

ticker = "CURRFX/USDJPY"
jsonObject = downloadJSONData(ticker, datetime.date(2014,11,1), datetime.date.today())
data = extractArrayOfDictionaries(jsonObject)
fileName = "D:\\Home\\Tableau\\" + ticker.replace("/", ".") + ".quandl.tde"
createTDEFile(fileName, data)

Nothing too difficult. I wrapped it up as a function call and can download data from python into TDE files.

A big help came from reading Scott Hanselman’s blog on the Python Tools for Visual Studio. While I am a complete novice in Python, I spend most of my day job buried in Visual Studio so keeping it all in there is great. And as you can now get Community Edition, it is a great place to do this experimenting.