In a new series of blog posts, what I creatively named as “Python Experiments in Tableau”, I will share a few projects that we are doing in-house to play with Python and Tableau. In the first example we will do something special with the “External Services” functionality: add live data from web services to implement up to date currency conversion calculations in Tableau. All this with only a few lines of code using thanks to TabPy and the Forex Python library.
TabPy provides a new extensibility option in Tableau that enables you to define table calculations using Python code in a workbook, which opens a whole new world to leverage advanced analytics, machine learning, and other cool python libraries to create the ultimate dashboard.
Note that you can install TabPy locally or on a remote server as well so you are able to utilize massive computing power besides your pc to do the heavy workload. After installing the server, all you have to do is connect to it by adding an External Service connection (Help > Settings and Performance > Manage External Service Connection) in Tableau Desktop.
Let’s test it with a basic python script.
Forex Python is a free library that provides foreign exchange rates and currency conversion, which uses the Fixer.io, a free API for current and historical foreign exchange rates published by European Central Bank. The rates are updated daily 3PM CET.
Once you have TabPy up and running install Forex Python using the following command: pip install forex-python or download it from github and copy the Forex Python library to Anaconda\envs\Tableau-Python-Server\Lib\site-package.
And we are done with all the installing and configuring, now let’s get to the fun part.
Currency Conversion using TabPy and Forex Python
Since we would like to dynamically change the sales values on the dashboard between currencies with an action filter, we have to create a string parameter field in Tableau that stores the currency codes, so we can use that as an argument later in our Python calculation.
Now we can create our new calculated field the same way as usual Tableau table calculations to store the values converted to target currencies. Here comes the magic, where we can start using the TabPy and Forex Python.
First, we will initialize the CurrencyRates class from forex_python.converter , so we can work with the currency rates, then we call the get_rate() function with two arguments, where the first argument is the source currency and the second is the target currency, to convert the values. In our case, all the sales values are stored in USD, so we simply use ‘USD’ as the first argument and the previously created parameter as the second argument, as you can see on the screenshot below.
And that’s it. All we have to do from now on is our regular DataViz magic in Tableau.
Here is a screenshot of an example dashboard displaying the sales figures of a furniture company by product category. As you can see it allows the user to switch between different currencies with a simple action filter using TabPy and the Forex Python library to convert the values from the base currency using up to date exchange rates from the European Central Bank.
The only question remaining is, how would YOU use TabPy to make better dashboards?
Source code and workbook: https://gitlab.com/brilliant-data/tableau-external-service
- Tableau Extensions Addons Introduction: Synchronized Scrollbars - December 2, 2019
- Tableau External Services API: Adding Haskell Expressions as Calculations - November 20, 2019
- Scaling out Tableau Extracts – Building a distributed, multi-node MPP Hyper Cluster - August 11, 2019