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.
