CayExcel - REST API Tutorial / Demo using Excel VBA

This is an Excel spreadsheet with some VBA code, that that gives you simple access to all your Cayenne data inside a spreadsheet with just your username/password, and a few clicks. You can load hour/day/week data series of your sensors, send data to Cayenne which can control your devices, and build fancy displays with text and graphics containing current values and history of your sensors.

It essentially gives access to your data without going through the website.
It is just a spreadsheet, not a service, so your information is just on your computer, interacting with mydevices.com databases.

The program displays all the REST GETs and POSTs, and then the raw REST responses, and then pulls apart the responses into the spreadsheets. And it creates nice Excel format timestamps for your timezone.

You have to download the spreadsheet from github, and then say Yes to running macros.
Of course, you can say no to macros, and read the code to see what it does first.

The green areas are for data entry, the orange is responses from Cayenne.





If you build some nice displays, you could post a screenshot of your artwork :grinning:

James

7 Likes

nice write up @jameszahary :+1:

When clicking get_one_datapoint() it says object required even when my Arduino Mega is selected. :frowning:

It shows Arduino Mega Channel 3 as the only drop down selection. But my photoresistor doesn’t appear.

Have a look at the Sensors tab - it should list all device/sensor names in column B and C, and then the device/sensor numbers in column D and E, and the channel number in column F.

On the Sheet1 tab, you should see the device sensor numbers written there at the top, and then inside the GET query, along with the long Authorization number.

There is only a dozen lines of code, so you can hit ALT-F11 (on Windows) and step through it.

Rev2 is underway with error checking, exception handling, and all that tiresome stuff. But also including the ancient history query, and hopefully the aws megaquery.

Looking for info on the MQTT subscription system … but perhaps there is no shortcut, and I have to learn all that. :grinning:

3 Likes

Updated CayExcel at github - same github link as above.

Added more error checking for bad data inputs, etc.

Also added Step 9 - General History query to fetch and graph data for for a bunch a pre-calculated data intervals (last 12 hours, last week, …) or any dates you choose to type in. To my surprise my data from last December is still in the database!

Question: Can anybody send me the “Hour” version of this query? This returns Minutes, but there must be a version for Hours???

https://platform.mydevices.com/v1.1/telemetry/af749630-e884-11e7-848a-61efd1c01e7d/sensors/ddb68760-e889-11e7-8934-ff70c6ef636b/summaries?type=custom&startDate=1533270373870&endDate=1533443173880

2 Likes

Updated CayExcel at github.

Version 3 adds the RAW data query to see the exact data (and time) your devices sent, rather than the 1 Minute summary in the previous general history query. Picture shows the four 15 second data points in the new query (step 10), versus the “1 Minute” summary (step 9)

Also added some stats on the previous 1 Minute query (step 9).

1 Like

Nice post! I’ll have to give this a try.

1 Like

we have updated the Rest API and this wont work to get the access token. Rest should work fine. @jameszahary can you make the needed changes to it.
If you want to change it, have a look at this topic How to access Cayenne API using Insomnia, Node-red and Thunkable

is there any specific thing that you want to use this? if yes, we can try find a way to work it out till @jameszahary is back.
if you want the data from cayenne, you can download it from the data tab on your web dashboard.

what do you mean by simple format. can you share a screenshot of what is the difficulty in the downloaded file.

can you test something? follow this steps and open the csv file [2021 guide] How to open CSV file in Excel
share the screenshot here once done.

you can try a much simpler way. have a look at this UTC time convert to IST time - #5 by muthukumar7103

New version at github - look for the file called CayExcel5.xlsm

The RAW data query is not working, nor the Refresh token system.

When adding AppKey enter as text (with single quote) '12345678 rather than number 12345678

Also adds a few more queries copied off the website, and creating graphs and storing the data in tabs.

I’ll come back to RAW query later.

Thanks @jameszahary for updating the git version.

You can check the How to access Cayenne API using Insomnia, Node-red and Thunkable for getting the refresh token. it works fine.
What do you mean by RAW data query?

I’m using the word RAW for the query in Part 10 on the spreadsheet.

Part 6 gets minute, hour, and daily averages for last-hour, last-day, last-month, etc., and Part 9 gets minute averages for a range of dates, but Part 10 gets actual 15 second samples, or binary events without going through the average or summary system. The actual events/data sent in the mqtt.

The Part 10 is the part of the website that downloads a csv after selecting the sensor and timeframe and giving a page of data on the screen, and then an entire query in the csv with the download.

My code started with a call to

https://delorean.mydevices.com/metrics/query?export=true&limit=0&offset=0&order=order+by+created_at+asc&scope=[" etc.

which returns a http link for amazonaws which in turn delivers the csv data file.

That used to work but it looks like something changed in that system.

You must have an internal document that covers such things???

The other question I had was that the first query that goes to delorean seems to have an sql like syntax embedded in it to select one of more sensors and specify the timeframe, etc. I was wondering if that also had the sql feature of “group by” that I could use to find the daily maximum for a sensor over a series of days or other sql features like count and sum for a certain range.

@jameszahary can you provide the exact SQL queries and endpoints you are using in the spreadsheet here?

Is CayExcel currently operating? Because I worked a month ago but now it is not operational

CayExcel uses REST API to fetch data from the cayenne server, which seems to have had a problem recently. It is working for me as I write this.

CayExcel is just software – it is not a thing that is operating or not. :grinning: Good to hear someone is using it!

1 Like

Updated github with version 6 for the new servers and syntax for new REST API.

CayExcel6.xlsm

As a bonus, the 1 month data limit seems to have expanded to 9 months – back to Sep 2019.

The raw data query not working … for the 15 second updates, rather than the 1 minute averages.

The data sending REST API not working, as it seems it was eliminated.

The DAY graph not working due to an current cayenne server issue.

1 Like

thanks @jameszahary for sharing it.