reading data from excel and summing it up

Hello. I'm having problems figuring out how to do this script:

I have an excel file containing: Dates, Time, Value

When I tried to read the excel file through Matlab, it changes the my 24hour time to decimal value. How do I keep it as 24 hour time values?

I need to somehow match the date and sum up values within every 15 minutes


12	10	9	13:01:00		0.01
12	10	9	22:36:52		0.01
12	10	9	22:40:27		0.01
12	10	9	22:42:41		0.01
12	10	9	22:45:37		0.01
12	10	9	22:47:25		0.01
12	10	9	22:51:12		0.01
12	10	9	22:54:25		0.01
12	10	9	22:58:04		0.01
12	10	9	23:01:38		0.01

So can anyone help me create a code to sum the 0.01 values for every 15 minutes and then combine the date that falls within the same time intervals?

Thank you
7/6/2010 2:07:04 AM
comp.soft-sys.matlab 208431 articles. 8 followers. lunamoonmoon (258) is leader. Post Follow

5 Replies

Similar Articles

[PageSpeed] 59
On Jul 6, 2:07=A0pm, "Andrew Liu" <> wrote:
> Hello. I'm having problems figuring out how to do this script:
> I have an excel file containing: Dates, Time, Value
> When I tried to read the excel file through Matlab, it changes the my 24h=
our time to decimal value. How do I keep it as 24 hour time values?
> I need to somehow match the date and sum up values within every 15 minute=
> Example:
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 13:01:00 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:36:52 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:40:27 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:42:41 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:45:37 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:47:25 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:51:12 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:54:25 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 22:58:04 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> 12 =A0 =A0 =A010 =A0 =A0 =A09 =A0 =A0 =A0 23:01:38 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.01
> So can anyone help me create a code to sum the 0.01 values for every 15 m=
inutes and then combine the date that falls within the same time intervals?
> Thank you

How is this post different from your post 3 days ago:

And have you tried the suggestions given you back then?
If so, show us your code and we'll help you with any problems.

mulgor (3007)
7/6/2010 2:38:08 AM

Thanks for responding. I couldn't figure it out before. I'm not quite sure how to go at it. So far I've only read the data. I'm not sure if I should read all the data first? Or one at a time (date, time, value)

I'm having trouble getting started. I was thinking of doing a "for loop" and "if statements". 

I took a class before, about a year ago, but I totally forgot how to write codes

clear all;

value = xlsread('Station_fire_tips_121009-061510-SF3', 'F2:F4098');
time = xlsread('Station_fire_tips_121009-061510-SF3', 'D2:D4098');

for i=1:4097 %number of rows


so far I only got that. so I need to read 4097 data. I just don't know where to start. i'm in desperate need of assistance.

Thank you for your time.
7/6/2010 3:05:04 AM
help anyone?
7/6/2010 3:42:22 PM
On Jul 7, 3:42=A0am, "Andrew Liu" <> wrote:
> help anyone?

Instead of sitting there waiting for others to solve YOUR problem, you
should be trying to solve it yourself.
It's pretty basic problem solving.  It's not rocket science.

What did you get when you executed those two xlsread commands?
Do value and time have any data in them?
If not, you need to read the help xlsread again and see what you have
missed out.
If they are not empty, what do the first few data look like?
Are they sensible?
If not, go back and read help xlsread again and see what you are doing
7/6/2010 7:58:54 PM
I'm not asking for anyone to solve my problem. I'm asking someone who can give me guidance and help. So far no one has provided any...


this is my code


clear all;

% input data
as = load('data.txt');

% the output file
fid = fopen('dataout.txt','w');

%assign data
date = as(:,1:3);
ppt_data = as(:,end);
time = as(:,4:6);
k = 0;

for i=1:length(time)
if time(i,1:3) < [13 15 0]
    for ii = 1
        k = k+1;
        sum(k,:) = sum(ppt_data(ii,:));
elseif time(i,1:3) > [22 16 0] & time(1,i:3) < [22 60 0]
    for ii = 1
        k = k+1;
        sum(k,:) = sum(ppt_data(ii,:));

So I have a list of time data which consists of 3 columns (each column corresponds to the hour, minutes, seconds, respectively). I'm trying to experiment with this data seeing if I can add values together that corresponds to the times (within 15 minutes of each other).

Also, with the times, I have dates, that corresponds to the times. The times are set in 24 hours time frame.
7/6/2010 8:13:06 PM
Similar artilces about - reading data from excel and summing it up:

Re: Can I read data using Macro? #3
Summary: Techniques for reading multiple files #iw-value=1 Ekta, Although macro can be used, there are probably better tools for what you have so far described. Take a look at trying something like filename q ("c:\junk\test\*msg.txt" "c:\junk\prod\my*.txt") ; If you need to know the where the data is coming from use the FILENAME option of the INFILE statement. If you need more control than an * provides then look at the FILEVAR option of the INFILE statement. If you still need macro thean give example problem not solved by the above. Ian Whitlock ================...

Algorith for DICOMDIR file reading
Dear friends What is the good algorithm for reading DICOMDIR files. I want the output as a tree structure. Thanks in advance. Alvin Dear friends, I got the solution. And I am closing this topic. Thanks and Regards Alvin ...

Reading text files.
I am looking for the easiest way possible to read a text file into SystemVerilog. First, a little background: I have six years of experience with VHDL, which I used to use to make command-driven testbenches. I could write testbenches that looked like this: RESET 10 IDLE 12 WRITE ABCD1234 DEADBEEF IDLE 6 READ 1234ABCD FEEDFEED etc. etc. etc. I would now like to do the same thing in Verilog and/or SystemVerilog, but cannot find an easy way to read and parse a simple text file. So far, the ONLY thing that I have found is: which ...

what if I only want to read the first line of an email?
I want Access to read the fresh part of an email but not the old replied content. What i mean is I want Access to only read the 'fresh' part of the email, for example 33333333333333 in this case. ----------------------------------------------------------------------------------------------------------------------- 3333333333333333 On Fri, Nov 13, 2009 at 4:11 PM, BBC <> wrote: 2222222222222222 On Fri, Nov 13, 2009 at 4:11 PM, ADD <> wrote: 1111111111111111 ---------------------------------------------------...

===Welcome to comp.lang.c++! Read this first. #23
Welcome to comp.lang.c++! Read this first. This post is intended to give the new reader an introduction to reading and posting in this newsgroup. We respectfully request that you read all the way through this post, as it helps make for a more pleasant and useful group for everyone. First of all, please keep in mind that comp.lang.c++ is a group for discussion of general issues of the C++ programming language, as defined by the ANSI/ISO language standard. If you have a problem that is specific to a particular system or compiler, you are much more likely to get complete and accurate answers in...

Re: Reading from excel #4
If you only need name and address, couldn't you try separating the field by the use of any number, or the word's "suite", "p.o", "appt", "apt", and "appartment" to identify the name? Might not be perfect, and wouldn't separate first and last names, but might provide a good start. Art -------- On Tue, 24 Jul 2007 17:49:27 -0400, Ed Heaton <EdHeaton@WESTAT.COM> wrote: >This brings to mind my adage - "It's easier to put values together than >to separate them!" > >Whoever created your file obviously di...

using Excel autofilter to exclude items
Hi, how do I exclude items using VBA code? I thought I would get the code using record macro but it only gave code to include items. eg Range("A13").Select ActiveSheet.Range("$A$13:$T$763").AutoFilter Field:=1, Criteria1:=Array( _ "10125014", "11394750", "2234200120", "2234600120", "52039100", "52080100", _ "54004912", "54004916"), Operator:=xlFilterValues I would like to exclude 3 items Colm > Hi, > > how do I exclude items using VBA co...

US-TX-Austin: .NET Programmer , ASP, VB, MSSQL, Strong Data; 4-6 mos. (45386332402)
US-TX-Austin: .NET Programmer , ASP, VB, MSSQL, Strong Data; 4-6 mos. (45386332402) ==================================================================================== Position: Programmer II/Programmer Reference: MLS00054 Location: Austin TX Duration: 4-6 mos. Skills: ASP.Net or VB.Net, SQL, Strong Data base background Scope: Convert applications from MS-Access front end to a .NET front end, Work with DBA to optimize MSSQL backends including DB design and Stored Procs, Comfortable working with sensitive and compartment...

Read string names in a loop
Hello I have a basic program question. I have some files that are named this way: test.7.2.20120718.101456.s5mubr.dcm test.7.2.20120718.101456.16501lh.dcm test.7.3.20120718.101456.8ll4jr.dcm and so on The last 6 characters before the extension is a string. And that part is always 6 characters. I have read file names with ascending integers before in a loop by using "%04d" or something similar. How can I read all of these file names in a loop? Thanks. - Anita On Wednesday, October 17, 2012 10:00:39 AM UTC+13, Anita S wrote: > Hello > > I have ...

How to read numbers in a text file efficiently
How to read the numbers in a text file in the following form ISSUE K1 K2 K3 "130108074" "4" "3" "2" "130108073" "6" "1" "1" "130108072" "2" "5" "1" "130108071" "2" "6" "5" "130108070" "1" "2" "2" "130108069" "5" "6" "1" "130108068" "3" "2" "1" "130108067" "5" "6" "5" ...

How to copy non-Palm Data from SD card to PC?
At home I have card reader. At work I have Palm cradle. I want to copy one file from home to work PC. I copied that file thru card reader to my SC card. Is it possible to transfer that file from SD card to my work PC using only Palm and USB cradle? Am Thu, 27 Nov 2003 08:11:30 +0100 schrieb mah: > Is it possible to transfer that file from SD > card to my work PC using only Palm and USB cradle? Look at CardExport from -- Stefan Braunstein - no e-mails - mah <> wrote in message news:<MPG.1a2fc20f7063aebf989788@news...

Reading data file containing character
Here's a data file ========================================================== NODE{1 0 6 0 1.000000e+002 -7.450605e-011 -7.450599e-011 -4.749746e-011} NODE{2 0 6 0 1.000000e+002 -7.450605e-011 -7.450599e-011 1.000000e-003} NODE{3 0 6 0 1.000000e+002 -7.450605e-011 -7.450599e-011 2.000000e-003} NODE{4 0 6 0 1.000000e+002 -7.450605e-011 5.857869e-004 1.000000e-003} NODE{5 0 6 0 1.000000e+002 -7.450605e-011 1.000000e-003 -4.749746e-011} NODE{6 0 6 0 1.000000e+002 -7.450605e-011 1.000000e-003 2.000000e-003} NODE{7 0 6 0 1.000000e+002 -7.450605e-011 1.514727e-003 1.000000e-003} NODE{8 0 6 0 1.0...

[telecom] Judge Questions Tools That Grab Cellphone Data on Innocent People
Judge Questions Tools That Grab Cellphone Data on Innocent People By Jennifer Valentino-DeVries October 22, 2012 A judge in Texas is raising questions about whether investigators are giving courts enough details on technological tools that let them get data on all the cellphones in an area, including those of innocent people. In two cases, Magistrate Judge Brian Owsley rejected federal requests to allow the warrantless use of "stingrays" and "cell tower dumps," two different tools that are used for cellphone tracking. The judge said the government sh...

Sybase goes read only
I have an Access97 frontend using linked tables to a sybase database via ODBC DSN. Sometimes but not always the linked table becomes read only. After refreshing the links it may or may not return to being editable. This happens on several tables in different DB's. Yes, there is a primary key on the tables No, exclusive locking is not employed. I cannot find either on Microsoft or sybase any refernece to this issue. My work around is to code data updates thru passthru queries, but clearly takes more coding. I checked to make sure we have the latest ODBC.dll Has anyone experienced the ...

Licensing issues
I have been asked to copy the compiler from a legacy 2.6 system to a solaris 10 zone. This compiler is used in production by us and is a Sun Workshop compiler. Using another compiler is anot an option. Sun as discontinued support for this product and offers a demo license to users. This demo license is perhaps forever. My issue is this: I followed Sun's instructions on installing the demo license keys. The customer says that they can compile without problems, However the license manager logs give errors. The errors stem from the fact that files were copied from the 2.6 system and are lic...

Advantages of Data Conversion and Data Formats Services by Data Entry India
Now a day's paper has become outdated and organizations are using online services to avoid paper work. For any organization, its biggest and most important asset is its data and more important is perfect accessibility of that data when it is needed. Sometimes it becomes really very difficult to manage such a large amount of data, especially for firms with large amounts of information, multiple formats, and complex system requirements. In order to avoid such situations of mismanagement conversion services are like bliss. Conversion services allow these organizations to systematize their dat...

Did you read about that?
you can take a look for this web sites or read this =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D What is Islam? ABOUT THE WORDS "ISLAM" AND "MUSLIM" The name of this religion is Islam, the root of which is S-L-M, which means peace. The word "Salam," derived from the same root, may also ...

open/fopen read/fread in multithreaded environment.
I am having data file, which is only used for reading by multiplethreads in same process. Reading may be done by in following possible way. 1.each Thread fopen,fread,fclose <fread may be called in loop> 2.each Thread open, read,close <fread may be called in loop> 3.Main Thread will fopen, childthread will fread in loop , mainthread will fclose at end. 4.Main Thread will open, childthread will read in loop , mainthread will close at end. <in 3 and 4 above return value of open/fopen will be global value <or may be pass to function but i think it is shared between multiple...

Re: Read binary data #7
sounds a bit cruel! packed numbers on zOS (eg from COBOL or PL1) are 1 byte longer than "necessary". They have a additional byte for the sign, in hex "0C"x or "0D"x as far as I remember. So a 8-digit number which should fit in 4 bytes will require 5 bytes. If there are some example bytes visible in hex, it might be possible to see what TBC (that's a sickness in Germany... a, ok TBCD!) stores. Maybe they omit the sign, cause a telefonnumber does't need it. The reason why they use BCD numbers might be, that there is space for leading zeros (?) and it's s...

Read XML from web into Notes as Text?
In Notes, I need to read in some XML that can be reached from a URL, and convert it to text. I've been using GetDocumentByURL to access the XML, but the document it returns has the XML attached as a file. So the only way I can find of accessing the file is to ExtractFile it, and store it on the file system. Then I'd have to open the file and read through it. Is there a better way of doing this? Can you read an attachment without having to detach and store it first? Any better approaches or suggestions anyone? Thanks. DAVE!! ...

Read MS Excel (binary, no CSV) file on OpenVMS?
I had a customer inquiry about being able to read an Excel binary file (not CSV) on OpenVMS. I Googled to see what was out there but didn't see if anything actually existed - anybody do this and want to share? Thanks! John Egolf wrote: > I had a customer inquiry about being able to read an Excel binary file > (not CSV) on OpenVMS. The CDA converters were, at their release, able to process the then current spreadsheet formats, but it has been somethging like 20 years since they were updated ... Directory $DISK1:[VMS$COMMON.SYSLIB] DTIF$READ_ASCII_FIELD.EXE;2 ...

Re: Data Step Recode: Is there a better way?? #2
Hello data _null_, Yes, you are correct on both counts. Sorry for the trouble you had determining what the output should look like. I am trying to code most significant source of HIV exposure. The hierarchy from top to bottom is: MSM/IDU MSM IDU Blood Endemic Heterosexual Other/Unknown So, for example, if a patient has exposure via: 1) men having sex with men and intravenous drug (MSM/IDU) and 2) Immigration from an HIV endemic region (Endemic), they will be assigned the code MSM/IDU. Thanks for your help. Paul Paul J. Miller, Ph.D. Research Scientist and Statistician Ontario HIV Treat...

Emails refuse to be marked as read
We use a cyrus imap server. Quite often a user has to mark their email as 'read' several times before it actually stays 'read'. There must be some configuration settings on the server which aren't correct but I don't know where to even start looking. Any ideas? rgds, Kristian. ...

Re: Nested Data Accesses? #12
On Tue, 20 May 2008 11:18:59 -0700, Laptop765@GMAIL.COM wrote: >Unfortunately I'm not able to provide actual code and/or data. ??? You do provide code, below. If your real data are proprietary or confidential, you can substitute made-up data which represent the problem. Often it's necessary to go this route even when working with public data, just to scale things down. >I was >more looking for general advice as to whether or not the code should >be working and any possible "obvious" mistakes I might be making... > >There are a few steps before the actual ...

Read all about it... OI! RISCOSitory! Read all about it!
Although I have been publishing the occasional bit of RISC OS related news on for a while now, it’s tended to be things which have caught my eye along the way: something spotted in a forum here, discussed at an event there, and so on. What there hasn’t been is any formal way to submit news and announcements to me that they may be considered for publication. As of today, that changes. News and announcements can now be submitted for consideration to The site continues to be one I maintain while also operating my day to day business, amongst many other thi...