Custom stock ticker tile (or any Google Sheets data)

I spent some time this week trying to figure out how to add stock data as a media tile and thought I'd share what I learned. There are a few steps involved and it's not as simple as I initially hoped, but it's also pretty flexible and could easily be expanded to other uses. Essentially, this can turn any Google Sheet into a media tile, so it can be used for much more than stock data.

Background

This started by wanting to display stock tickers as a media tile. I first came across a great post on being able to turning any json data into a badge, but this had the limitation of only being able to display one piece of data. I wanted to be able to display a few different tickers in one tile.

https://support.actiontiles.com/en/communities/12/topics/2707-live-traffic-travel-time-tile-image-from-query-text-using-shieldsio

(As an aside, Google provides instructions for getting JSON feeds from spreadsheet data: https://developers.google.com/gdata/samples/spreadsheet_sample)

I also came across this post explaining how to create a media tile from a stock chart, but that also wasn't quite what I wanted: https://support.actiontiles.com/en/communities/12/topics/3014-stock-ticker-tile

Part 1 - Create a public link to Google Sheets data

Example spreadsheet - https://docs.google.com/spreadsheets/d/1Id-49n-MnAG6MpzrI3WKAuXXaosg7WVR6PFBIhh8ryY/edit#gid=0

First create a spreadsheet that contains whatever information will be displayed in the tile and share it (File->Share) so it doesn't require a login to view. Then take the spreadsheet ID (the long string in that URL) and sheet ID (the part after gid=) from the link to that sheet and insert them into this link below. You can also specify a range of cells if you only want to use a portion of the sheet. (Reference)

https://docs.google.com/spreadsheets/d/SpreadsheedID/pubhtml?single=true&gid=SheetID&range=D15:E15&widget=false&chrome=false&headers=false

For this example, the link is:

https://docs.google.com/spreadsheets/d/1Id-49n-MnAG6MpzrI3WKAuXXaosg7WVR6PFBIhh8ryY/htmlembed?single=true&gid=0&range=A2:C5&widget=false&chrome=false&headers=false

This link can be used to create an iFrame tile under "My Shortcuts" in ActionTiles and you can stop here if that's all you need. The main shortcoming is that shortcut tiles don't auto update until you refresh the browser. You'll also need to adjust the width and height of the cells so that it fits well into the tile. 

If you want the tile to update without refreshing the browser and adjust its size automatically to fit the cell, then it needs to be turned into an image that can be used for a media tile which has a refresh rate.

Part 2a - create an image to be used as a media tiles

This part requires running a couple command line tools on a Linux server. I have a Raspberry Pi set up that I use for this.

The first tool is wkhtmltoimage which converts any webpage to an image. This supports creating svg, jpg, bmp, or png images. I found that svg is the best quality. The command is:

wkhtmltoimage --crop-w 224 --crop-h 112 --height 1024 "$imgURL" $outFile.svg

crop-w - the width of the spreadsheet range

crop-h - the height of the spreadsheet range

height - this just needs to be larger than crop-h. I found that the default value used was too small

imgURL - the public link to the spreadsheet URL

outFile - where the resulting image is stored

Here is the command I use for this example which outputs the image in a file called stock_example.svg

wkhtmltoimage --crop-w 224 --crop-h 112 --height 1024 "https://docs.google.com/spreadsheets/d/1Id-49n-MnAG6MpzrI3WKAuXXaosg7WVR6PFBIhh8ryY/htmlembed?single=true&gid=0&range=A2:C5&widget=false&chrome=false&headers=false" stock_example.svg

Part 2b - Create a link to the image

Now I use rclone to add the image to my Google Drive, but this also supports other services like Dropbox and Amazon Drive. (If you have your own web server, you could also just copy the file there and not worry about using rclone.) The command for rclone is:

rclone copy <outFile> <driveName>:<folder>

outFile - the path to the image that was created in the last step

driveName - this should be the name of the drive you set up when configuring rclone

folder - the name of the folder on my Google Drive where the image gets saved

For this example (with a drive gdrive and folder ActionTiles) the command is:

rclone copy stock_example.svg gdrive:ActionTiles

Part 2c - Automatic updates

Note: Here I explain how to share the image using Google Drive, but this could also be done with any service that rclone supports like Dropbox or Amazon Drive.

The last step is to automate all of this so that the data is updated periodically. To do that, I put the wkhtmltoimage and rclone commands into a script called updateStocks.sh and then set up a cron job to run at the desired interval. Here's my cron entry which runs every 10 minutes while the market is open (9-4 Mon-Fri)

*/10 9-16 * * 1-5 /home/pi/actiontiles/updateStocks.sh

Then in Google Drive, you need to share the image publicly and you'll get a link like this:

https://drive.google.com/file/d/15bxoWJeH4fbyzUz9G44I72Tly4nE0fkt/view?usp=sharing

The direct link to the image takes the below format, inserting the proper ID:

https://drive.google.com/uc?id=IMG_ID

So in this example the direct link to the image is:

https://drive.google.com/uc?id=15bxoWJeH4fbyzUz9G44I72Tly4nE0fkt

Then in ActionTiles, use that link for a media tile and set the refresh 600 seconds (or whatever refresh rate you prefer).

Some final notes

• One of the coolest parts of this is that any changes you make to the spreadsheet (values, colors, size, etc.) will automatically show up in ActionTiles. No extra steps are needed after the initial configuration.
• The width and height of the cells in your spreadsheet should be modified so that they fit nicely in a tile
• I also made the background color of my cells match the color used by the ActionTiles theme
• When generating the .svg image, thin cell borders are added. I haven't found a way to get rid of this
• wkhtmltoimage seems to cache the site. Sometimes it takes a few minutes after updating the spreadsheet before the image produced from this tool is updated

If you made it this far, thanks for reading. Please comment if you have any questions or come up with other ideas of how to use Google Sheets to create interesting tiles!