make calculations with excel file data

I'm a newbie in matlab, so can anyone help me in the following question?

I want to use the data of a excel file in a matlab script.

I already import data with the following code (weather data file):

[fileName,pathname] = uigetfile({'*.xlsx'},'Select Location');
  nomeficheiro=strcat(pathname,fileName); 
  [a,b,c]=xlsread(nomeficheiro, 'A2:AJ8762'); 
  location = c(1,2);
  set(handles.location_text,'String',location);

In this example I get the location of the weather data using the value store in row 1 and column 2.

In this file in the column 8, we have 8760 hourly values of ambient temperature.

I need to do a calculation with all the values.

For example, import value row 1 and column 8, make calculation in matlab script, next import value row 2 and column 8, make calculation in matlab script, next import value row 3 and column 8, make calculation in matlab script,... and so on....

I want to automate this calculation

I want to store the results of the calculations in textbox in gui.

Thanks

[dpb]

All the numeric data are in the numeric array a; in Matlab one generally tries to write code to make use of the fact that it can handle full arrays at a time.

What are the calculations you wish to perform on the data in column 8? If it is the same for each, then it will be quite simple; if there's something different for various ones, you'll have to have a way to know which and what do do for each...

[Joaquim Monteiro]

Is the same calculation for all. This is hourly data (8760 values) of a year. I've to do the calculation, then store the result and then go to the next row of the column 8.

Answer 1

[dpb]

"_Is the same calculation for all...of the column 8."_

Tf=a(:,8)*1.8+32;  % convert column 8 (assume T in C) to F

Done for the whole year in one swell foop...that's the power of Matlab; no loops needed most of the time and are to be avoided when not.

Above gives you a new variable; if you don't need to keep the original but want to overwrite, then simply

a(:,8)=a(:,8)*1.8+32;  % convert column 8 (assume T in C) to F

Since you still haven't made us privy to the actual calculation, I just made up an example; simply replace the specific with whatever yours is.

[Joaquim Monteiro]

In my case the column 8 represents the hourly ambient exterior temperature of a place. What I want is to use each value to calculate a power consumption.

In my case for example if Tamb is the value of column 8 I want to make something like this.

For first row:

Tamb = 7,2C

Pc = Tamb + 253,2 + I % in this case Pc is power consumption of the equipment and I is the Solar Irradiation that is store in the same excel data file in column 10.

After this calculation I want to repeat the process to the second row:

Tamb = 7,0C

and so on...

After this I want to sum the 24 values (represents 1 day) and put that in a textbox in the GuI.

Maybe is a very easy process to do, but I'm a very newbie user of matlab.

[dpb]

Indeed, it is easy..

Pc=a(:,8)+253.2+a(:,10);

I think you need to open the documentation and begin at the "Getting Started" section and work through the tutorial sections on the basics of Matlab syntax, array addressing and so on. It's a pretty thorough introduction done by example.

[Joaquim Monteiro]

And how can for example sum the 24 values calculated?

[dpb]

PcTot=sum(reshape(Pc,24,[])).';

I'll leave it as "exercise for the student" to use a smaller case of a vector of length N to be summed over a subset of M (N divisible by M) groups. Try, for example, N=8 and M=2 so you can test the result and see the values to see the "how" and "why" it works. The data could be x=rand(8,1);, for example.

HINT: The key is in the Matlab array storage order of being column major and that sum and other Matlab functions by default work over the first dimension or by column. Again, this is outlined in the "Getting Started" tutorial (albeit the use of storage order which the above "trick" uses isn't explicitly demonstrated but that is simply a utilization of understanding memory order).

[Joaquim Monteiro]

Thanks dpb.

Now I understand the logic.

[Joaquim Monteiro]

Just another question.

If I want to use the result of a row calculation as input of the next row. For example:

Pc1=a(:,8)+253.2+a(:,10);

Then to calculate Pc2:

Pc2=Pc1-a(:,8)+253.2+a(:,10);

and so on...

Do I have to make such as 8760 lines of code for this?

[dpb]

NO!!! Try it and see...Matlab is not Excel.

Now, if you want to add the result to a spreadsheet you'll have to write it as at the moment of calculation the result exists only in memory...

[Joaquim Monteiro]

dpb I don´t understand the comment.

[dpb]

I don't understand what's not to understand... :)

Have you done the first step? If so, you should have the newly-computed arrays in memory you can see at the command line with

whos Pc*

which will result in showing you the type and size of the arrays. They should be 8760 rows long for Pc and 365 for PcTot--that happens automagically in Matlab by virtue of its array syntax. Unlike Excel wherein you have to copy every result into a separate cell, Matlab deals with all values in an array in one expression.

Again I refer you to the "Getting Started" section of the documentation for details of Matlab syntax and examples of how it works in tutorial form...

[dpb]

After rereading your original question to refresh my memory on what you had done so far, I noticed the last comment of "store the results of the calculations in textbox in gui." That's likely going to be a difficult user interface -- a text box box with 8760 or even the 365 lines is just too much data to look at conveniently that way. Is there really any reason someone will want to see all those individual values, even? I'd suggest plotting them instead or summarizing even further to a set of descriptive statistics or the like such as [mean, mode, min, max, ...] along with the associated date or similar. That is succinct enough to show and likely will have all the info someone will need on the process interactively.

However, whether you choose the latter or think you must go with the full monte, use a table not a text box.

doc uitable

But, "I don't do windows" so I'm not the best person to ask about gui components in depth altho I think the general precept above is definitely true that large lists of values in a gui are, generally speaking, useless. Doing such is the result of the unfortunate ubiquitousness of the spreadsheet paradigm...

[Joaquim Monteiro]

Sorry, maybe I don't put the question in the right away.

What I need is to use the 8760 hourly values to make calculations.

After I have to use the result of the calculation of row 1 as input to row 2, and results of row 2 as input to row 3 and so...

After that I what to aggregate the values in monthly totals and display in gui. (so in my gui I have 12 textbox for this propose).

At this moment my difficult is to use the result of a row calculation as input of the follow row.

[dpb]

"use the result of a row calculation as input of the follow row."

Pc1=a(:,8)+253.2+a(:,10);
Pc2=Pc1-a(:,8)+253.2+a(:,10);

The above reduces to

Pc2=a(:,8)+253.2+a(:,10)-(a(:,8)+253.2+a(:,10));

which is identically zero.

Show a (short) segment of the data in the two columns of interest and what the actual calculation you want to be for a few rows--three or four should be enough.

It seems to me the result is already calculated but perhaps I indeed don't follow what you're trying to actually compute.

[Joaquim Monteiro]

I attached the weather Data file. Previously I tell it wrong, I want to use the column 16 and not the column 10.

As you can see in the file, I've the hourly data for the 8760 hours of the year. I'm trying to calculate the hourly power consumption of a heat pump. For that I've to start the calculation in the first row and store the result (later I want to sum the hourly values to get monthly totals and show in a specific textbox in gui. I've one for every month). Until here everything is fine, because already you explain how to do this and I understand.

My problem know is that after the first calculation (1st row), the result is use as input for the 2nd row, and after the result from the 2nd row is use in the 3rd row and so on... In other words I'm trying to do a dynamic simulation, so that if I change the weather data the program automatically calculates new power consumption values.

From what I read maybe I've to use the loop capabilities of matlab, but I'm not certain if that is the right way and how to do that.

[dpb]

Again, without a specific computation I don't know but reading between the lines of your previous computation where you wrote

Pc2=Pc1-a(:,8)+253.2+a(:,10);

mayhaps what you're looking for is

PcDelt=diff(Pc);

which will be the differential between observations. Note, of course, as a difference there will be one less observation so you may want to augment the result with a dummy value at the beginning to keep the length consistent with the raw data--

PcDelt=[0; diff(Pc)];

[Joaquim Monteiro]

See if can get to it now.

[dpb]

No, it's still just a link, not an attachment...but it really isn't the file that's important, it's the description of what the calculation you're stumped at really is...I was just suggesting you take a few lines and demonstrate specifically what you are trying to calculate and post that as a workaround the attempt to only describe the desired result.

Did my above guess regarding diff hit the mark?; you didn't comment at all on it.

[Joaquim Monteiro]

Sorry I was very busy in the last couple of days. You can see the code that I've have till now. I already can display the power consumption of a particular day (in this case day 1 as example (Pd1)). Now I want to use the result of row as the input of the next one. I don't know how to to this automatically.

[fileName,pathname] = uigetfile({'*.xlsx'},'Select Location');
  nomeficheiro=strcat(pathname,fileName);
  [a,b,c]=xlsread(nomeficheiro, 'A2:AJ8762');
  location = c (1,2);
  set(handles.location_text,'String',location);

    Pc=a(:,8)+253.2+a(:,16);
    TPc = sum(Pc);
    set(handles.sttankcapacity,'String',TPc);
    TPd = sum(reshape(Pc,24,[]));
    Pd1 = sum(Pc (1:24));
    set(handles.dhwtemperature,'String',Pd1);

[Joaquim Monteiro]

There's the data.

10 2011   1   1  10  60 ...  7    3.8  80 100201  409 1413  301  251  625   69 32803 78625  9914
11 2011   1   1  11  60  ... 8.3  4.1  75 100201  536 1413  319  356  697   90 46892 89034 12932
12 2011   1   1  12  60 ...  9.5  4.5  71 100201  602 1413  325  416  736  101 55111 94819 14512
13 2011   1   1  13  60 ... 10.3  4.7  68 100201  602 1413  329  416  736  101 55111 94819 14512
14 2011   1   1  14  60 ... 10.6  4.7  67 100201  536 1413  330  356  697   90 46892 89034 12932

I want to do

Pc1 = value column8 + 253,2 + column16

This result is use as a input to the 2nd row

Pc2 = Pc1*((value column8 + 253,2 + column16 )-(value column20)
Pc3 = Pc2*((value column8 + 253,2 + column16 )-(value column20)

[Joaquim Monteiro]

PORTO =
 1.0e+05 *
Columns 1 through 12
  0.0001    0.0201    0.0000    0.0000    0.0001    0.0006       NaN    0.0001    0.0000    0.0008    1.0020    0.0041
  0.0001    0.0201    0.0000    0.0000    0.0001    0.0006       NaN    0.0001    0.0000    0.0008    1.0020    0.0054
  0.0001    0.0201    0.0000    0.0000    0.0001    0.0006       NaN    0.0001    0.0000    0.0007    1.0020    0.0060
  0.0001    0.0201    0.0000    0.0000    0.0001    0.0006       NaN    0.0001    0.0000    0.0007    1.0020    0.0060
Columns 13 through 20
  0.0141    0.0030    0.0025    0.0063    0.0007    0.3280    0.7863    0.0991
  0.0141    0.0032    0.0036    0.0070    0.0009    0.4689    0.8903    0.1293
  0.0141    0.0032    0.0042    0.0074    0.0010    0.5511    0.9482    0.1451
  0.0141    0.0033    0.0042    0.0074    0.0010    0.5511    0.9482    0.1451
>> Pc1=PORTO(1,8)+253.2+PORTO(1,16)
Pc1 =
885.2000
>> Pc2=Pc1*(PORTO(2,8)+253.2+PORTO(2,16))-PORTO(2,20)
Pc2 =
 8.3553e+05
>> Pc3=Pc2*(PORTO(3,8)+253.2+PORTO(3,16))-PORTO(3,20)
Pc3 =
 8.3443e+08

and so on until Pc8760

Answer 2

[dpb]

OK, that's one interpretation but seems unlikely that that's what you really, really intend...that's going to "blow up" to an extremely large number in the end...

But, for the above as written simplest coding is in a loop although there are ways to vectorize it, will leave those for more advanced lesson particularly as I don't expect you're going to like the result as it will overflow in all likelihood by the time you reach the end...

>> pc=d(:,8)+253.2+d(:,16);
>> for i=2:length(pc),pc(i)=pc(i-1).*pc(i)-d(i,20),end
pc =
 1.0e+05 *
  0.0089
  8.3553
  0.0100
  0.0100
  0.0096
pc =
 1.0e+08 *
  0.0000
  0.0084
  8.3443
  0.0000
  0.0000
pc =
 1.0e+11 *
  0.0000
  0.0000
  0.0083
  8.3401
  0.0000
pc =
 1.0e+14 *
  0.0000
  0.0000
  0.0000
  0.0083
  8.0132
>>

Above shows the same results as your calculation with the entry of each loop in range of the display each iteration.

I don't understand what it is you're actually physically trying to compute so can't judge the appropriateness of the formulation for the purpose. BUT, note that pc(1) has units of whatever d(8) and d(20) are but you've then squared that value and added another term in only units(d). Unless these are dimensionless, that's clearly in error for a physical system computation.

[Joaquim Monteiro]

Thanks dpb for your patient.

It's what I need. It's truly a dimensionless parameter.

Now I can move forward with my work.

[dpb]

Hmmm....does it stay bounded over the array length?

>> p=pc(1);
   for i=1:8760
     p=p*975;
     if (realmax/p)<975,break,end
   end
   fprintf('%d %e\n',i, p)
102 6.691326e+307
>>

So, assuming the rest of the data are similar to those provided, 975 is roughly the average of the first half-dozen or so values and hence is the ratio of pc(N+1)/pc(N) and so the estimate of how large a value you can compute is given by the above at only a few over 100 observations.

Methinks, somehow, this can't be correct, whatever it is.

[Joaquim Monteiro]

Dpb, you are right, the modelling that i need at this moment are not complete, but is sufficient for now to move forward.

[dpb]

OK, just so's you recognize the issue...