Tablueprint 1: My US Stock Portfolio

Tablueprint 1: My US Stock Portfolio

tablueprint-featureTablueprints is a series where I share how to make my Tableau data visualizations. If you would like updates on future posts, be sure to subscribe. I will only email when I have something new to share and I will not share your email with anyone.

In this installment, I share:

– Thoughts on mobile-first design in Tableau and working with Device Designer
– How to use Quandl as a data source for US stocks
– How to use the IMPORT functions in Google Sheets
– How to automatically refresh a Google Sheets document
– How to use Tableau to union different tabs in a Google Sheet
– How to automatically refresh a Tableau Public workbook with a Google Sheets connection

For more information about the visualization featured in this installment, see My US Stock Portfolio before reading further.


How to use Quandl as a data source for US stocks

There are many good choices when it comes to data sources for US stocks. I was recently introduced to Quandl, which I found particularly useful for this project because (1) the account is free and (2) there are several options for how to export the data. This came in handy when I wanted to import the data into Google Sheets (more on that later).

To use Quandl as your data source, start by navigating to quandl.com and signing up for a free account.

From within your account, assuming you want to use US stock data, navigate to:

DATABASES in the top navigation > Stock Data > United States > Stock Prices End of Day, Current and Historical > Wiki EOD Stock Prices

After the Wiki EOD Stock Prices database is open, you can search for any US stock symbol. To export the data for each stock, find the page of the stock you are interested in, then use the export option of your choice in the right navigation. For My US Stock Portfolio, I used CSV exports.
msft-export-data-quandl

 

How to use the IMPORT functions in Google Sheets

After I recorded the CSV links for the 12 stocks I wanted to track, it was time to create my data source in Google Sheets. I chose to use Google Sheets because Tableau Public 10 has the ability to automatically refresh connections to Google Sheets on a daily basis.

Google Sheets have several IMPORT functions which allow you to grab data from the web and display it in a spreadsheet. Since I am working with CSV files from Quandl, I used the IMPORTDATA function to create my data source.

I could have gone tab by tab and placed the IMPORTDATA code in cell A1, but instead, I wrote a simple script to do it for me. Please note that I am not a JavaScript developer by trade. I say that for two reasons: (1) I know you can do this even if you don’t have a coding background (2) please don’t judge the efficiency of my code.

Before I created the script, I created 12 blank tabs in my Google Sheet. These are the tabs that would eventually be filled with the data for my 12 different stocks. While I was creating this data source, I learned that Google Sheets has a 2 million cell limit. After deleting some unneeded columns, I was able to import the data for all 12 stocks, but beware of this limit in case you are trying to bring in even more data.

To add a script in Google Sheets, from within a Google Sheet, navigate to Tools > Script Editor…

The script I used for one of the tabs is:

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.setActiveSheet(ss.getSheets()[0])
var range = sheet.getRange(‘A1’);  range.setValue(‘=importdata(“https://www.quandl.com/api/v3/datasets/WIKI/[ENTER YOUR OWN STOCK SYMBOL].csv?api_key=[ENTER YOUR OWN API KEY”)’)
}

This script opens the first tab in the workbook, navigates to cell A1, then does an IMPORTDATA function to populate the tab. Note there are places in the script to add your own stock symbol and Quandl API key.

If you have more than one stock, you would simply copy and paste everything between the { } brackets and update the number between the [ ] brackets and the stock symbol in the URL. Replacing the number between the [ ] brackets is very important because this is the tab where the data will populate. The tab numbers start at 0. So for three stocks, my script looks like this:

function myFunction() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.setActiveSheet(ss.getSheets()[0])
var range = sheet.getRange(‘A1’);  range.setValue(‘=importdata(“https://www.quandl.com/api/v3/datasets/WIKI/[ENTER YOUR OWN STOCK SYMBOL].csv?api_key=[ENTER YOUR OWN API KEY”)’)

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.setActiveSheet(ss.getSheets()[1])
var range = sheet.getRange(‘A1’);  range.setValue(‘=importdata(“https://www.quandl.com/api/v3/datasets/WIKI/[ENTER YOUR OWN STOCK SYMBOL].csv?api_key=[ENTER YOUR OWN API KEY”)’)

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.setActiveSheet(ss.getSheets()[2])
var range = sheet.getRange(‘A1’);  range.setValue(‘=importdata(“https://www.quandl.com/api/v3/datasets/WIKI/[ENTER YOUR OWN STOCK SYMBOL].csv?api_key=[ENTER YOUR OWN API KEY”)’)
}

This script opens the first tab and places the data for the first stock starting in cell A1. Then it opens the second tab, and places the data for the second stock in cell A1 of the second tab. Then it opens the third tab, and places the data for the third stock in cell A1 of the third tab.


How to automatically refresh a Google Sheets document

Now that we have a script, there is one last step to automatically run the script. After saving your script, navigate to Resources in the top navigation of the script editor and click “Current project’s triggers”. You will see a message that says “No triggers set up. Click here to add one now.” After clicking on the message, you will get an intuitive interface to choose how frequently to refresh your data. If you want to immediately refresh your Google Sheets document without waiting on the trigger, you can always navigate to Run in the top navigation of the script editor.


How to Union a Google Sheets data source in Tableau Public

Now that I had a Google Sheet with my 12 different tabs of stock data, it was time to connect to the data in Tableau. Both Tableau Desktop and Tableau Public have the ability to connect to a Google Sheets data source and perform unions. This workbook was made completely in Tableau Public 10.

To connect to the data, open Tableau Public 10, click “More…” under the “To a Server” options in the left navigation, and choose “Google Sheets”.

tableau-public-10-google-sheets-connector

After entering your Google credentials, you will have the ability to choose the Google Sheet you want to work with. After choosing the appropriate Google Sheet, the data source editor will open. To union the different tabs of your Google Sheet, drag “New Union” from the left navigation into the main window. Then left click and drag each sheet that you want to use into the Union box:

tableau-public-union-data-editor

When you create a union in Tableau, an extra column will be added to your data to tell you what sheet the data came from. In my case, the data on each tab represents a different stock, so I right-clicked on the column that Tableau added called “Sheet”, and renamed it to “Stock”.


Thoughts on mobile first design in Tableau and working with Device Designer

Once I had the data for My US Stock Portfolio, the actual making of it was straightforward; each stock widget has three components: a title bar, a crosstab, and a sparkline. The one tricky thing about the design is that each widget is built with a horizontal layout container inside of a vertical layout container. I started by floating a vertical layout container on the dashboard for each stock. I then placed the title bar for each stock into the vertical layout container. In the horizontal space below the title bar, I added a horizontal layout container that contains the crosstab and sparkline.

What I think makes this visualization work well is its clean layout. To make every component of this dashboard the perfect dimensions, I started by seeing what dimensions would look best on a phone. To do this, I navigated to the new Device Designer to see what dimensions I would need for a phone. To create a device-specific design, from within a dashboard sheet in Tableau, click on “Device Preview” in the top left corner of the left navigation; then change the “Device type” to “Phone”. By default, the phone layout will be for a “Generic Phone” which is 375 pixels wide by 667 pixels tall.

The height of my stock widgets were not that important as I could really pick any height that would fit all three components of each widget. The width was very important because I wanted the widgets to fit on a phone without having a need to scroll horizontally, and this device preview is how I determined the best width for each widget. If the width of a generic phone is 375 and I wanted 25 pixels of padding on each side, I knew the width should be 325 (375 – 25 – 25 = 325).

Since I had the width determined and chose a height, I could back out the math to create the dimensions for the desktop version. For example, if I wanted the desktop version to display three widgets across, I would take 325 + 325 + 325 + 100 pixels worth of padding = 1075.

The desktop version displays a three column by four row table of stock widgets while the phone version displays a single stacked column:

tablueprint-my-us-stock-portfolio-mobile

To learn more about using Tableau’s Device Designer, see the post: Designing Device-Specific Dashboards in Tableau 10.


How to automatically refresh a Tableau Public workbook with a Google Sheets connection

Lastly, to automatically refresh a Tableau Public workbook with a Google Sheets connection, be sure to embed your credentials when you save the workbook to Tableau Public:

tableau-google-sheets-refresh-and-embedAfter the workbook is saved to Tableau Public, you should also check that you have checked the box to refresh the workbook daily. You can see this by clicking “Edit Details”, which will take you to the options for the workbook:

tableau-public-google-sheets-refreshAs long as you’ve checked the box that says “Keep my data updated with google sheets every day”, Tableau will automatically refresh your workbook once per day. If this daily Tableau Public refresh happens after your Google Sheets trigger has updated your Google Sheet, your workbook will have the most current daily data. In my experience with this new features of Tableau Public so far, the refresh happens at 2 AM Central Time.

Thanks for reading,
– Ryan


By | 2017-04-21T22:10:19+00:00 September 20th, 2016|Tableau Tips|