Blog post banner

Streaming data into Excel from Raspberry Pi devices via realtime backends Pusher and PubNub

August 11, 2018

Pusher and PubNub are realtime backends that are commonly used for communication with Internet of Things devices, receiving geotracking updates, and many other use cases. Together with Gridarrow they can provide a very effective way to build live dashboards in Excel.

If you’re reading this blog post, you’ve almost certainly heard of the Raspberry Pi, but if not, it’s a small and affordable computer that is often used as the onboard controller for home and industrial automation. They’ve been used in everything from robotic tea pots to virtual co-pilots designed to prevent aircraft crashes.

In this blog post we’ll show how easy it is to get realtime data from internet connected devices like the Raspberry Pi to Excel using Gridarrow.

What is Gridarrow?

Gridarrow is the missing server for Excel. It connects just about any data source to Excel with simple Python scripts.
Why is it awesome?
  • It's real-time, so as soon as the data is updated in the Gridarrow App it's updated in Excel, no need to poll or refresh.
  • It's multi-user so your colleagues can connect to the same data at the same time.
  • It simplifies your spreadsheets by moving data retrieval and pre-processing into Python.
Request Demo

What is a realtime backend?

Realtime backends such as Pusher and PubNub are hosted services that can be thought of as cloud Pub/Sub broker (although they may offer certain value added features such as PubNub Functions that allow the transformation of messages in flight).

They work on essentially the same principle:

  1. Publishers send messages to a channel on the realtime backend.
  2. Subscribers register an interest in a channel, and get notified when messages appear.

When used together with Gridarrow, the Gridarrow App takes the role of the subscriber, listening for messages, and updating the Grid when it receives something of interest. This diagram should explain things:

Streaming data from Raspberry Pi to Excel

Both Pusher and PubNub provide high quality client libraries, in a range of languages. Using Python on the client side is very straightforward of course - you need to import the library and provide a few authentication details. Providing the updates is then a matter of calling pusher.trigger or pubnub.publish.

Here’s an example of using both client libraries, and you can read more about how to install the libraries on Raspberry Pi for Pusher and PubNub:

 1 2 3 4 5 6 7 8 91011121314151617
# The event data
update = dict(id='my-device', status='online', timestamp=datetime.now().timestamp())

# Pusher
from pusher import Pusher              
pusher = Pusher(APP_ID, KEY, SECRET, CLUSTER)
pusher.trigger(channels='device-status', event_name='status-event', 
               data=update)

# PubNub
from pubnub.pnconfiguration import PNConfiguration
from pubnub.pubnub import PubNub
config = PNConfiguration()
config.subscribe_key = "sub-key"
config.publish_key = "pub-key"
pubnub = PubNub(config)
pubnub.publish().channel('device-status').message(update).sync()

Getting the data into Excel

Now that the messages are being published to the realtime backend from the clients running on Raspberry Pi, it’s up to the Gridarrow App to subscribe for the events and send the data to Excel. The code for doing that is pretty straightforward, but rather than going through it here, we’ve published example Apps for both Pusher and PubNub to the Gridarrow Catalog, so please check them out! We’ve also included the full source for the Pusher version below.

Finally, here’s an example where we’re simulating the Raspberry Pi client running in a few Windows cmd shells, and streaming the data via Pusher and Gridarrow into Excel:

Streaming data from Raspberry Pi to Excel

But before you jump into it, remember to:

 1 2 3 4 5 6 7 8 91011121314151617181920212223242526272829303132333435363738394041
import json
import os
from datetime import datetime
import pysher
from gridarrow import Arrow, ArrowError, Indexer, DefaultValue

arrow = Arrow()

PUSHER_CLUSTER = "eu"
PUSHER_KEY = os.environ['PUSHER_KEY']
PUSHER_SECRET = os.environ['PUSHER_SECRET']

if PUSHER_KEY == '' or PUSHER_SECRET == '':
    raise ArrowError("Please obtain a Pusher API Key and secret"
                     "and add to the App secrets")

PUSHER_CHANNEL = 'device-status'
PUSHER_EVENT = 'status-event'

@arrow.on_start
def start(_):
    pusher = pysher.Pusher(cluster=PUSHER_CLUSTER,
                           key=PUSHER_KEY, secret=PUSHER_SECRET)
    grid = arrow.grid("updates", rows=20,
                      columns=["id", "status", "timestamp"],
                      default_value=DefaultValue.EMPTY_STRING)
    indexer = Indexer()

    def on_event(event):
        data = json.loads(event)
        row_num = indexer[data['id']]
        grid[row_num, grid.columns] = [data['id'],
                                       data['status'],
                                       datetime.fromtimestamp(data['timestamp'])]

    def on_connect(_):
        channel = pusher.subscribe(PUSHER_CHANNEL)
        channel.bind(PUSHER_EVENT, on_event)

    pusher.connection.bind('pusher:connection_established', on_connect)
    pusher.connect()