Export doubletimeseries dataset from MATLAB to Excel

Hi, I have data from Simulink out.conout colum time and 7 others:

I want to export this to excel. I've tried writetable, xlswrite and other functions but do not yet get the desired results. Can anyone help?

[Mathieu NOE]

hello

can you share a mat file containing your data and what format you want to export ?

[DB]

Thank you. I want to export data obtained by Simulink to Excel (.xls). Hereby my MATLAB and Simulink files.

[Mathieu NOE]

hello

I was a bit surprised that your simulink files has a mix of continuous and discrete blocks - i assume this is done on purpose.

The only thing I did on the simulink file is to desactivate the export of out, tout, simout etc... and other stuff that fills the workspace, as you have already the conout and disout arrays - which are the only data we need to plot & export;

i also modified a bit the m file so we have explicit names for time and data , and these will be used also for the timetable export;

you will see that we export in 2 steps as we have two batches of data with different time vectors.

of course you could also interpolate one batch of data on the other time vector if that makes the excel file simpler (one single common time vector) and more readable

hope it helps !

everything sent back to you in the attachement

[DB]

@Mathieu NOE Thank you so much for this effort. Is there also a way to set d1,d2 on the same time t, but with values only changing at t=5? or should this be done manually in MATLAB/Excel?

[Mathieu NOE]

hi

if I understand correctly , you would like to use time1 (used for x1 and x2) to interpolate d1 and d2 , but keep value of d1 and d2 constant (equal to their first value) between t= 0 and t = 5 s ?

I also wonder if when you interpolate, you want to keep the stair case style (constant plateau) for d1 and d2 or do we do a classic linear interpolation ?

[DB]

The discrete d1 and d2 update every 5t, but I want the values to d1 and d2 show up at every t in the data, just as x1 and x2 do. So yes, I want it to stay at their first value at t=5 as on t=6,7,8,9 for example.

The d and x stand for different things, it is no interpolation. I want to keep the staircase-like plot.

[Mathieu NOE]

hello

so this is a modified code , d1 and 2 keep the stair case "look", but are sampled with time1

clear all
close all
n=100;
x1=12;      %household with high energy usage (in kWh per day)
x2=7.57;    %household with just above average daily energy usage (Netherlands)
x0=[x1;x2];
% a11=0.8;
% a12=0.2;
% a21=0.3;
% a22=0.7;
a11=0.70;
a12=0.10;
a21=0.20;
a22=0.65;
A=[a11 a12; a21 a22];
% b1=0.1;
% b2=0.15;
% B=[b1 0;0 b2];
b11=0.19;
b12=0.01;
b21=0.00;
b22=0.15;
B=[b11 b12;b21 b22];
c1=0.8;
c2=0.6;
C=[c1 0;0 c2];
D=eye(2)-C;
x=nan(2,n);
x(:,1)=x0;
d0=x0;
Ts=5;
% Ab=[A(1,1)*(1-sum(B(1,:))) A(1,2)*(1-sum(B(1,:)));
%     A(2,1)*(1-sum(B(2,:))) A(2,2)*(1-sum(B(2,:)))];
% L=[1 -1;-1 1];
sim('impexsim');
time1 = conout.Time;
x1 = conout.Data(:,1);
x2 = conout.Data(:,2);

time2 = disout.Time;
d1 = interp1(time2,disout.Data(:,1),time1,'previous');
d2 = interp1(time2,disout.Data(:,2),time1,'previous');

figure(1)
plot(conout)
title('Impulsive dynamics of x1 and x2')
ylabel('Energy usage in (kWh)')
xlabel('Time t in (days)')
hold on
plot(disout)
legend('x1(t)','x2(tk)','d1(t)','d2(tk)','location','southeast')
ylim([5 13])

figure(2)
plot(time1,[x1 x2 d1 d2]); % must be the same as fig 1 

% as all data are now resampled with time1 common time base , 
% we will save to excel in one steps
T1 = table(time1, x1, x2, d1, d2)
writetable(T1,'myData2.xlsx','Sheet',1,'Range','A1:E100')

[DB]

Thank you! However, the script stops as there is an error with time1=conout.Time :

Unable to resolve the name conout.Time.

[Mathieu NOE]

run with the attached file

[DB]

Hi @Mathieu NOE ,

It may be because I have changed my Simulink model but the first and second plot are not equal. See below. Therefore, The data is wrong. I have attached my files below.

[Mathieu NOE]

hello

no there is no bug in the code , the visual difference comes from the fact that the first plot is made of staircase display based on data available at t = 0, 5, 10 etc ... the plot will show a horz line between 0 and 5, etc

when we do interpolation with option 'previous' , the interpolated y values include horizontal sections of data that stops one sample before t = 5 or 11à, 15 ... , you can check the output of interp1 function (and it appears also in the excel )

in other words , interp1 cannot create two y values at the same t = 5,10, ... s time steps

we could tweak the code for only the sake of plotting exactly the same thing, but anyway, what do we export in the excel spreadsheet ? only one y value can be stored at t = 5,10, ... s time steps

[DB]

Thank you for your quick response. The staircase plot needs to be maintained to show impulsive dynamics. I understand what you're saying. Do you mean this plot cannot be transferred to a table because the value of y will have two values?

I wanted to export to Excel to make a nice graph, instead of the plain scope or matlab plot. If this doesn't work properly, what do you suggest to design the plots?

[Mathieu NOE]

No , I said that interp1 interpolation do not have an option to duplicate y values for the x = 0,5,10 time stamps

we have to do it ourself with a home made function , not complicated indeed

i'll try to do it quickly

otherwise, you can tweak the matlab plot to make it nicer , up to you

[Mathieu NOE]

see new script attached , it duplicated the intermediate points so that it looks again as staircase;

it should do the same if you plot the data in excel

[Mathieu NOE]

still I believe you wished that now both time series share the same common time vector, which can only be done if the first series gets also duplicates at t= 5,10,...

see if I can do that still today (if interest ? )

[DB]

@Mathieu NOE Thank you for your eagerness to help! I have not cheched your previous suggestion yet, but I think you are right on your last comment. I would like to have them in the same time vector.

[Mathieu NOE]

hey , success !!

here is it !

excel output in attachement, could easily plot the staircase data

clear all
close all
n=100;
x1=12;      %household with high energy usage (in kWh per day)
x2=7.57;    %household with just above average daily energy usage (Netherlands)
x0=[x1;x2];
% a11=0.8;
% a12=0.2;
% a21=0.3;
% a22=0.7;
a11=0.70;
a12=0.10;
a21=0.20;
a22=0.65;
A=[a11 a12; a21 a22];
% b1=0.1;
% b2=0.15;
% B=[b1 0;0 b2];
b11=0.19;
b12=0.01;
b21=0.00;
b22=0.15;
B=[b11 b12;b21 b22];
c1=0.8;
c2=0.6;
C=[c1 0;0 c2];
D=eye(2)-C;
x=nan(2,n);
x(:,1)=x0;
d0=x0;
Ts=5;
% Ab=[A(1,1)*(1-sum(B(1,:))) A(1,2)*(1-sum(B(1,:)));
%     A(2,1)*(1-sum(B(2,:))) A(2,2)*(1-sum(B(2,:)))];
% L=[1 -1;-1 1];
sim('impexsim');
time1 = conout.Time;
x1 = conout.Data(:,1);
x2 = conout.Data(:,2);

time2 = disout.Time;
d1 = interp1(time2,disout.Data(:,1),time1,'previous');
d2 = interp1(time2,disout.Data(:,2),time1,'previous');
  
figure(1)
plot(conout)
title('Impulsive dynamics of x1 and x2')
ylabel('Energy usage in (kWh)')
xlabel('Time t in (days)')
hold on
plot(disout)
legend('x1(t)','x2(tk)','d1(t)','d2(tk)','location','southeast')
ylim([5 13])

% figure(2)
% plot(time1,[x1 x2 d1 d2]); % must be the same as fig 1 

figure(3)
[time11,x11,x22,d11,d22] = mystaircase(time1,x1,x2,time2,d1,d2);
plot(time11,[x11 x22 d11 d22]); % must be the same as fig 1 

% as all data are now resampled with time11 common time base , 
% we will save to excel in one step
T1 = table(time11,x11,x22,d11,d22)
writetable(T1,'myData2.xlsx','Sheet',1,'Range','A1:E100')

function [time11,x11,x22,d11,d22] = mystaircase(time1,x1,x2,time2,d1,d2)
% data to replicate corresponding to t = 5 / 10 /15 (remove first and last
% index of time2 before)
[LIA,LOCB] = ismembertol(time1,time2(2:end-1),1e-6);
time11 = [];
d11 = [];
d22 = [];
x11 = [];
x22 = [];

for ci = 1:numel(LOCB)
   if LOCB(ci) < 1
        time11 = [time11; time1(ci)];
        d11 = [d11; d1(ci)];
        d22 = [d22; d2(ci)];
        x11 = [x11; x1(ci)];
        x22 = [x22; x2(ci)];
   else % here we duplicate the values 
        time11 = [time11; time1(ci); time1(ci)];
        d11 = [d11; d1(ci-1);  d1(ci)];
        d22 = [d22; d2(ci-1); d2(ci)];
        x11 = [x11; x1(ci-1); x1(ci)];
        x22 = [x22; x2(ci-1); x2(ci)];
    end
  
end

end

[DB]

@Mathieu NOE Thank you so much for your effort!

[Mathieu NOE]

Glad it helped !

would you accept my answer ?

[DB]

Glad to! But I don't see the button?

[Mathieu NOE]

I have to put my suggestion into the answer section, so you can accept it

Answer 1

[Mathieu NOE]

so official answer below :

------------------------------------

hey , success !!

here is it !

excel output in attachement, could easily plot the staircase data

n=100;
x1=12;      %household with high energy usage (in kWh per day)
x2=7.57;    %household with just above average daily energy usage (Netherlands)
x0=[x1;x2];
% a11=0.8;
% a12=0.2;
% a21=0.3;
% a22=0.7;
a11=0.70;
a12=0.10;
a21=0.20;
a22=0.65;
A=[a11 a12; a21 a22];
% b1=0.1;
% b2=0.15;
% B=[b1 0;0 b2];
b11=0.19;
b12=0.01;
b21=0.00;
b22=0.15;
B=[b11 b12;b21 b22];
c1=0.8;
c2=0.6;
C=[c1 0;0 c2];
D=eye(2)-C;
x=nan(2,n);
x(:,1)=x0;
d0=x0;
Ts=5;
% Ab=[A(1,1)*(1-sum(B(1,:))) A(1,2)*(1-sum(B(1,:)));
%     A(2,1)*(1-sum(B(2,:))) A(2,2)*(1-sum(B(2,:)))];
% L=[1 -1;-1 1];
sim('impexsim');
time1 = conout.Time;
x1 = conout.Data(:,1);
x2 = conout.Data(:,2);
time2 = disout.Time;
d1 = interp1(time2,disout.Data(:,1),time1,'previous');
d2 = interp1(time2,disout.Data(:,2),time1,'previous');
  
figure(1)
plot(conout)
title('Impulsive dynamics of x1 and x2')
ylabel('Energy usage in (kWh)')
xlabel('Time t in (days)')
hold on
plot(disout)
legend('x1(t)','x2(tk)','d1(t)','d2(tk)','location','southeast')
ylim([5 13])
% figure(2)
% plot(time1,[x1 x2 d1 d2]); % must be the same as fig 1 
figure(3)
[time11,x11,x22,d11,d22] = mystaircase(time1,x1,x2,time2,d1,d2);
plot(time11,[x11 x22 d11 d22]); % must be the same as fig 1 
% as all data are now resampled with time11 common time base , 
% we will save to excel in one step
T1 = table(time11,x11,x22,d11,d22)
writetable(T1,'myData2.xlsx','Sheet',1,'Range','A1:E100')
function [time11,x11,x22,d11,d22] = mystaircase(time1,x1,x2,time2,d1,d2)
% data to replicate corresponding to t = 5 / 10 /15 (remove first and last
% index of time2 before)
[LIA,LOCB] = ismembertol(time1,time2(2:end-1),1e-6);
time11 = [];
d11 = [];
d22 = [];
x11 = [];
x22 = [];
for ci = 1:numel(LOCB)
   if LOCB(ci) < 1
        time11 = [time11; time1(ci)];
        d11 = [d11; d1(ci)];
        d22 = [d22; d2(ci)];
        x11 = [x11; x1(ci)];
        x22 = [x22; x2(ci)];
   else % here we duplicate the values 
        time11 = [time11; time1(ci); time1(ci)];
        d11 = [d11; d1(ci-1);  d1(ci)];
        d22 = [d22; d2(ci-1); d2(ci)];
        x11 = [x11; x1(ci-1); x1(ci)];
        x22 = [x22; x2(ci-1); x2(ci)];
    end
  
end
end