You may have heard of TabPy or using tools like R and Matlab in Tableau calculations, but did you know you can achieve similar results with just a simple REST-based application? This can be a great way to leverage APIs and JavaScript libraries and can be easier if you are more familiar with JavaScript than Python or other languages. Whether it’s geocoding, looping calculations, or up-to-date stock data (like I’ll show you today) analytics extensions allow you to connect to other services and bring that power and data into your dashboards in real-time. In this tutorial, I’ll show you how you can set up a simple Node.js server for getting up-to-date stock ticker data. Once you know the basics, you’ll be able to create your own functions and services for whatever you like!

Requirements

• Access token for a stock data API - I’ll be using the Finnhub API where you can get a free access token that allows 60 requests per minute on their free tier.

• Node.js server - For this tutorial, I’ll be using Glitch for coding and hosting my server. Of course, you can run your Node.js server anywhere you like or just locally. To help you get started you can either remix and run this template in Glitch or download it and run it locally. I'll be using Express to set up routes, take a look at the Express website if you want to learn more.

• Tableau Desktop or Tableau Server/Online web authoring - You will need to be able to edit calculations and worksheets.

Set up and test the server

Once you've gotten the prerequisites all set, it's time to start setting up the extension server. At this point, your server should be up and running (either in Glitch or locally with the command node server.js.) If you used the template from above you can go ahead and delete the public and views folders since this extension will not have any visible web pages. For this tutorial, we will be working mostly on the server.js file so start there. Make sure to change the GET / function to return something other than the index page, since it no longer exists. For example:

app.get('/', function (request, response) {
  response
    .status(406)
    .send(
      '<p style="text-align:center;margin:50px;font-family:Benton Sans, Helvetica, Arial;">This app only works in Tableau.</p>'
    );
});

Now that we've gotten rid of the pieces we don't need the first thing we want to do is make sure Tableau is able to send and receive information to and from our server. First, we need to be able to correctly parse the messages incoming to our server from Tableau. To do this we'll set our express app to use JSON by adding the following after creating the app:

app.use(express.json());

Note: If you are on 2020.1 Tableau may be sending requests as x-www-form-urlencoded, if that is the case you can use app.use(express.text({ type: "*/*" }));. Just make sure to parse the JSON once you receive it using JSON.parse(request.body).

Now that we are parsing the incoming data correctly we need to allow Tableau to get information about our analytics extension. To do this, Tableau sends out a GET request for information to an /info endpoint. This is where we tell Tableau some basic information about the extension. If you require authentication, this would be the place to tell Tableau. For today we're going to keep it simple and just return some basic data but you can find more information about the parameters for this payload in the Analytics Extension documentation.

app.get('/info', function (request, response) {
  let info = {
    description: 'A Tableau Analytics Extension for getting stock prices.',
    creation_time: '0',
    state_path: 'https://stock-data-ae.glitch.me/',
    server_version: '1.0.0',
    name: 'Live Stock Data',
  };
  response.status(200).set('Content-Type', 'application/json').send(info);
});

Here we have added a GET /info function for our app that creates an info object with the required items and sends it as a response to Tableau. At this point, we are able to test the extension in Tableau and we should be able to get a successful connection. To do this, open Tableau Desktop, and in the File menu go to Help > Settings and Performance > Manage Analytics Extension Connection.... Depending on your version you are then going to select either
TabPy/External API or Analytics Extensions API. Next, enter your server's URI (I suggest not including HTTP/HTTPS or any trailing slashes) and port then set the SSL checkbox to match your server. Once everything is entered, click the Test Connection button and if everything is set up correctly you should get a success message! If you get an error double-check your work or take a look at the completed project at the end of this post. If you are not using Tableau Desktop, no worries you can also add the analytics extension to Tableau Server and Tableau Online.

Receive the data

Now that we know Tableau can communicate successfully with our extension we can start receiving data. Before we begin it is important to note that Tableau will send a query to our extension every time the data changes. As long as the calculation using our extension is being used, every filter update or pill placement will trigger the extension. In this simple example that isn't a problem but can be an issue if you are using an API that has a limit or is expensive to run. One way you can mitigate some of the unnecessary requests is to implement some rate-limiting in the extension. I'm not going to go into detail in this blog post, but this is something I added to the analytics extensions live sample that you can find on glitch. Now that that's out of the way let's get to the good stuff! When Tableau sends data to our extension it will be sending it as a POST request to an /evaluate endpoint and it will pass two objects in the body: script and data. The script object contains the text that the user wrote into the calculation as a part of the script function. The data contains one or more arrays of data containing data from the marks on the worksheet. In this case, that data will be a list of stock tickers that we can then use with the Finnhub API to get their latest prices. First, we'll add a new POST /evaluate endpoint to our app where Tableau by default will send us the data.

app.post('/evaluate', function (request, response, next) {
  const data = request.body.data;
  const script = request.body.script;
});

Within this endpoint function, we're first going to grab the script and the data so we can access them easily. From here on, the way that you set up your function is really up to you. I'll be showing you a very specific way to set it up but the script that your users pass to the extension can contain any string at all. If you've used TabPy before this would be the Python script that you wanted to execute in your Python server. In this simple example, I'm going to use the script as a place where users can enter what type of stock data they want to get back. Specifically, I'll be giving options for current, high, low, open, and previous close prices. In the Finnhub API, all of these can be returned in one request so I just need to choose which data to send back. Keep in mind Tableau can only accept a single array as a response. Since Finnhub uses single letters to represent each price, I'll be creating an object to translate more human-friendly labels to their Finnhub letters.

const priceSelection = {
  current: 'c',
  high: 'h',
  low: 'l',
  open: 'o',
  close: 'pc',
};

Now that we're set from the script side, let's get the data side ready. The Tableau SCRIPT functions that are used to pass data to external services allow for multiple columns of data to be passed. Each column of data is passed as an array with the first column as _arg1, the second column as _arg2, and so on. In today's example, we should only be receiving one column of data, the stock tickers, so we can focus specifically on _arg1.

const tickers = data._arg1;

Now that we have the array of tickers isolated and the type of price data the user wants to return we're ready to use the Finnhub API!

Getting prices from the API

If you take a look at the Finnhub API documentation for getting stock price quotes you'll see that we need to make the requests one at a time. Since this API has limits we want to reduce the number of times we send requests by getting rid of any duplicates. There are many ways to do this but here is a short method using array spreaders and sets:

const uniqueTickers = [...new Set(tickers)];

Here we are converting our tickers array into a Set, which by default is unique, and then "spreading" it out back to an array. Next, we need to go through each unique ticker, get its price data from Finnhub, and store them in a variable. To make the request I'll be using node-fetch. Add this to your server either by using npm i node-fetch locally or going to the package.json file in Glitch and searching for it in the Add a package drop-down in the top left. Next, bring it into the project by adding const fetch = require("node-fetch"); near the top of the server.js file. Now we can start to fetch the data we need.

let tickerPrices = {};

for (let ticker of uniqueTickers) {
  const response = await fetch(
    `https://finnhub.io/api/v1/quote?symbol=${ticker}&token=${process.env.FINNHUB_TOKEN}`
  );
  const body = await response.json();
  tickerPrices[ticker] = body;
}

Here's what's going on:

1. After deduplicating the tickers we then create a new object, tickerPrices, to contain the ticker price data.
2. Next, we loop through the unique tickers and make a request to the Finnhub API.
3. Note that the URL used is a template string with a variable for the current ticker in the loop as well as your Finnhub API token. To keep this token safe we can leverage the environment variables. In the .env file add a line as follows: FINNHUB_TOKEN=youractualtokenhere. You will then be able to reference this token using process.env.FINNHUB_TOKEN
4. The fetch call we are making is asynchronous so don't forget to include an await before the method as well as an async on the overall function. To learn more about promises and async/await take a look at this great tutorial.
5. Finally, we are adding items to the tickerPrices object for each ticker and price data object.

Send prices to Tableau

Now that we have the pricing data it's time to format it in a way that Tableau will accept. This means passing back a single array that has the exact same number of values as the original arrays that Tableau sent. (For future reference, sending an array with one value is also acceptable.) For this, we can use the original tickers array to create the output array.

let output = [];

for (let ticker of tickers) {
  output.push(tickerPrices[ticker][priceSelection[script]]);
}

response.status(200).send(output);

Here we've created an empty array we can fill and are looping through each original ticker and grabbing the appropriate data to pass back as an array of values. Finally, we can send the output array back as our response to Tableau's request.

Try it out!

At this point your analytics extension is complete, so let's try it out in Tableau! Feel free to either create your own workbook with stock tickers or use this sample workbook based on Mad Money Stock Picks. To use the extension create a new calculation with the following formula:

SCRIPT_REAL('current', MIN([Ticker]))

There are three important things here:

1. We are using SCRIPT_REAL because the extension is returning floating-point values.
2. We're passing in the type of pricing data we want to see. Remember this is what we created the priceSelection object for. Try out different prices!
3. We're passing in the field that has the tickers that we will receive as an array. Note this must be aggregated.
   Now that you have the calculation, drag it to a worksheet that has your Ticker field on it and see the latest stock prices!!

At this point, you have the tools you need to build out a full analytics extension to suit your needs. You can find all the code for this tutorial below. To see a more detailed example check out my live sample on Glitch. Comment below if you tried this out! Learned something new? Subscribe and never miss a new post!