Jump to content
  • 0

Transmitted FibaroHC2 event data in Excel sheets


Question

(Vienna, 28.10.2015)

 

On YouTube (recommend): "https://youtu.be/3Nz_C10Nvt4"

and Download (zip): "https://www.dropbox.com/s/vv5x2ugf4c7e4tk/SendFibaroEventDataToExcel_EN.zip?dl=1"

 

This is my way to transmit the fibaro-event-data in Excel spreadsheets.

 

Since I am a FibaroHC2-newbie, I have to read a lot of forum posts first:
http://forum.fibaro.com/ (english)   http://siio.de/board/ (german)
And the descriptions in the developer documentation are an important tool:
https://developer.fibaro.com/ (english)
The forum members @petergebruers (expert of forum.fibaro.com) and @crissxcross (moderator of siio.de/board) told me several times friendly the right way.

 

I have found in Fibaro HC2 system is no way to write the event data in an external file.

One option was to send the data via email to me.
But this grief I did not want to do to the Fibaro server.

Therefore, I use as a temporary store global variables.
The global variables of Fibaro system are very powerful.
In the global variable "selEventData" I have saved 1,024 kilobytes of data for testing purposes.
These were 20,749 records. With this value (20,000 event-records), I have limited my system.
Where the real limits lie, everyone can try out for themselves.

After the selection, so the data is available in the local variable "selBasicData" (sections, rooms and devices) and "selEventData" (events) are available.
A local variable can be exported with the "HC2 Toolkit program" in a plain text file. Click the right mouse button on the line with the desired variables and select "Export".
Remove the unnecessary parts at the beginning and end of the file and give it the extension ".csv".
Now the data can be easily imported into an Excel spreadsheet and processed further.

 

This is perhaps not the best solution.

But for me the development was interesting and I learned a lot about the FibaroHC2-system.
I wish you also so much fun with your Fibaro system!

Link to post
Share on other sites

7 answers to this question

Recommended Posts

  • 0

It's always good to learn something.

 

I'm not sure though, why you would want an Excel with the events? Just curious.

 

A tip would be that the event data is readily available, without setting a global variable. At this URL: http://<fibaro IP>/api/panels/event?last=10&type=id. This actually show the last 10 events, but you can get more if you want. Documentation for this feature is located at developer.fibaro.com. I bet you can import that into Excel directly, without an intermediate export tool (just a hunch, haven't really done that myself). Anyway, thanks for sharing, and enjoy!

Link to post
Share on other sites
  • 0

@HansS is right. The solution is complicated. I did not see the forest for the trees. The solution, the @HansS describes outputs a JSON table. I'll try the event data directly from Excel (Visual Basic Macro) to retrieve a HTTP request. But the creation of this first version in LUA was not meaningless for me. I have learned a lot about the Fibaro system and LUA scenes.
And for you it was an example of how not to do it;-)

Why Excel? Calculations, Graphical statistics, pivot tables, etc.

Link to post
Share on other sites
  • 0

@HansS: many thanks for the hint. It really worked...in couple of second I was able to get the last 100.000 entries.

One question, sorry I'm still discovering things...How should I interpret the timestamp? A number like "timestamp":1453829941 doesn;t look quite friendly.

Thanks

Link to post
Share on other sites
  • 0

@HansS: many thanks for the hint. It really worked...in couple of second I was able to get the last 100.000 entries.

One question, sorry I'm still discovering things...How should I interpret the timestamp? A number like "timestamp":1453829941 doesn;t look quite friendly.

Thanks

This is the amount of seconds since 1.1.1970.

To convert this number:

=COLUMN_ID_HERE/86400+DATE(1970,1,1)
Link to post
Share on other sites
  • 0

(Vienna, 28.10.2015)

 

On YouTube (recommend): "https://youtu.be/3Nz_C10Nvt4"

and Download (zip): "https://www.dropbox.com/s/vv5x2ugf4c7e4tk/SendFibaroEventDataToExcel_EN.zip?dl=1"

 

This is my way to transmit the fibaro-event-data in Excel spreadsheets.

 

Since I am a FibaroHC2-newbie, I have to read a lot of forum posts first:

http://forum.fibaro.com/ (english)   http://siio.de/board/ (german)

And the descriptions in the developer documentation are an important tool:

https://developer.fibaro.com/ (english)

The forum members @petergebruers (expert of forum.fibaro.com) and @crissxcross (moderator of siio.de/board) told me several times friendly the right way.

 

I have found in Fibaro HC2 system is no way to write the event data in an external file.

One option was to send the data via email to me.

But this grief I did not want to do to the Fibaro server.

Therefore, I use as a temporary store global variables.

The global variables of Fibaro system are very powerful.

In the global variable "selEventData" I have saved 1,024 kilobytes of data for testing purposes.

These were 20,749 records. With this value (20,000 event-records), I have limited my system.

Where the real limits lie, everyone can try out for themselves.

After the selection, so the data is available in the local variable "selBasicData" (sections, rooms and devices) and "selEventData" (events) are available.

A local variable can be exported with the "HC2 Toolkit program" in a plain text file. Click the right mouse button on the line with the desired variables and select "Export".

Remove the unnecessary parts at the beginning and end of the file and give it the extension ".csv".

Now the data can be easily imported into an Excel spreadsheet and processed further.

 

This is perhaps not the best solution.

But for me the development was interesting and I learned a lot about the FibaroHC2-system.

I wish you also so much fun with your Fibaro system!

 

hello

excellent work. i have managed to get this working first time but after i get my first set of data the VD doesnt update the labels properly at all?

eg

when i change the device ID or roomID or sectionID the labels do not update.

this also happens with the time selection ie To and From times i cannot see them change?

 

however they do update within the global variables themselves as i pressed the button and refreshed the data in the toolkit and i saw the values being updated.

 

so is there some issue with my HC2 or browser because the labels are not being updated?

i have tried on chrome and safari, i also used the fibaro app on my phone and it still didnt update the label texts?

thanks

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...