ThingSpeak : Create some useful formulas

Background

In a previous post related to the thingspeak integration for my solar panel production monitoring, I created some basic dashboard showing the production throughout the day. I wanted to see what was the production since the beginning of the day. Since ThingSpeak is part of MathWorks (MATLAB), I have a good feeling that some strong formulas were available for our creativity. 

MATLAB

Indeed, ThingSpeak is part of MatWorks and then some MATLAB capabilities can be leveraged. I did some researches and yes! I was not disappointed. You can indeed add the power of MATLAB to the ThingSpeak data you are collecting. MATLAB language is far away from my university times and yes, there is some basic learning to remind, but after a couple of reading and forum digging, I could go somewhere.

First, you need to access your channel on ThingSpeak and from there, go to the App Menu and Choose MATLAB analysis :

From there, you can create your formulas.

Start from a blank template :

Then, you can copy/paste the code below into the MATLAB Code section.

You need to adjust a few things : provide your read and write API keys in the relevant variable as well as your channelID. You also need to adjust the source data field ID number and the update interval. In my case, I'm writing data every 25 seconds. I'm using field4 as target for my result. Be sure to have field4 ready to accept the data. This number is hardcoded at the end of the script, so feel free to adjust it to whatever you are using.

% _____________________________________________________________

%

% _______ _______ _______ _____ _______ ______

%| | | _ |_ _| |_| _ | __ \

%| | | | | | | | __ <

%|__|_|__|___|___| |___| |_______|___|___|______/

%

% Solar Production aggregator

% (c) 2023 - Frederic Lhoest - Let's Talk About Tech

% https://www.lets-talk-about.tech/ - @flhoest

% _____________________________________________________________

%% ====================

%% Initialize variables

%% ====================

% Define ThingSpeak channel and API key

channelID = your_channel_id;

% Define ThingSpeak API Read key

readKey = 'your_read_api_key';

% Define ThingSpeak API Write key

writeKey = 'your_write_api_key';

% Define data interval

intervalInSec= 25;

% Define field ID for production value

fieldID = 2;

%% =================================

%% Innitialize time and timezone

%% =================================

% Get current date and time

%now = datetime('now', 'TimeZone', 'Europe/Brussels');

now = datetime('now');

% Compute start and end times for current 24-hour period

start_time = datetime(now.Year, now.Month, now.Day, 0, 0, 1);

end_time = datetime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second);

%% =====================================

%% Retrieve data from ThingSpeak channel

%% =====================================

% Retrieve data from ThingSpeak channel

url = sprintf('https://api.thingspeak.com/channels/%d/fields/%d.json?start=%s&end=%s&timezone=CET&apikey=%s',channelID, fieldID, datestr(start_time, 'yyyy-mm-dd HH:MM:SS'), datestr(end_time, 'yyyy-mm-dd HH:MM:SS'), readKey);

response = webread(url);

% Extract production values from response

data = [response.feeds.field2];

% Convert string to array

% Define the delimiter as CR LF

delimiter = '\r\n';

% Use strsplit to split the string into an array of items

production_values = strsplit(data, delimiter);

% Convert string values to float

production_values = str2double(production_values);

%% ========================================================

%% Compute the data and convert them into prodcution in kWh

%% ========================================================

% Compute sum production in watts but first need to cleaup the string from CR LF

% Init larger variable for the total production

total_production = 0;

for i = 1:numel(production_values)

% Remove unwanted characters (space, CR/LF)

tmp=strtrim(string(production_values(i)));

% Convert the new value into double

tmp=double(tmp);

if isnumeric(tmp)

% If value is different than NaN take it into account

if ~isnan(tmp)

total_production = total_production + tmp;

%fprintf("In loop %f\n",tmp);

end

end

end

% Convert into kW

total_production = total_production / 1000;

% Calculate time interval

time_interval_hours = length(production_values) * (intervalInSec/3600);

% Calculate production in kWh

total_production =

    (total_production / length(production_values)) * time_interval_hours;

%% ==========================================

% Write results into another ThingSpeak field

%% ==========================================

% Write total production to ThingSpeak channel

data = struct('api_key',writeKey,'field4',total_production,'channel_id',channelID)

webwrite('https://api.thingspeak.com/update',data)

When the code is adjusted and you are ready, click on save and run. You should see similar output : 

The result is showing field4 is receiving 7.6400, it means since the sunrise, we have produced 7.64 kWh. 

Now, we need to add the result on the channel main screen !

From your channel, choose Add Widgets

Next, select the Numéric Display format for our new widget

Now, we need to configure the widget parameters. Give it a name, choose the field containing the data we want to display, leave update interval default, unit is kWh and I want to see 2 digits accuracy.

Push the create button and there we go, the value is displayed in our main channel dashboard.

This is nice, but we need to make sure this data is regularly updated. There is a way to schedule the formula calculation. To avoid hitting the ThingSpeak limit of a free account, we have to make sure we do not refresh the field too often. In my case, I'm refreshing the data every 5 minutes.

So, coming back to the MATLAB code, at the very bottom, once in edit mode, you can see a Schedule Action section, we are interested into the TimeControl option.

These are the values on my side for your reference : 

Now, the widget containing the field 4 value in your channel dashboard will be updated every 5 minutes. How cool is this ? ;)

Conclusion

ThingSpeak is very powerful and it surprises me every day. To me this is a great tool for collecting sensor data and create some analysis directly within the same platform. No need to install any fancy tools on your machine or being limited by the platform, you can actually do the same with a mobile phone or a tablet without limitations.

Now, let's talk a bit about the code I wrote. How does it work. It takes all the data from the current day until the current time. I do some cleanup (removing line feed and non printable characters) and then I exclude the values containing zeros - yes they will impact the average if I do that. Then I average all the values and it gives me a result in Watts. As soon as I divide the result by 1000 I have kW. I also need to convert the result from kW per second into kW per hour, this explains the division by 3600. I did some comparison with my home automation platform and the results are very close, so far I'm happy with the value and I do not see any reason to add more accuracy at this time.

I still have some ideas for more formulas, let's see that in a later post.

As always, I hope this helps ! ;)