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.
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.
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???
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)
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.
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.
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.
