how to calculate average, mean and last price of date strings

Hi,

I have a time series of date times with mileseconds and what I want to do is to keep time stamps for each second (without mileseconds) with corresponding values of max-min and last price in second.

For example for the following data

2017.01.04 10:24:55.493      Price  max            min  last price in second
2017.01.04 10:24:56.219  117.662500  117.662500  117.661000  117.661500
2017.01.04 10:24:56.300  117.662000      
2017.01.04 10:24:56.423  117.661000      
2017.01.04 10:24:56.492  117.661000      
2017.01.04 10:24:56.554  117.661500      
2017.01.04 10:24:56.647  117.661500      
2017.01.04 10:24:56.750  117.661500      
2017.01.04 10:24:56.985  117.661500      
2017.01.04 10:24:57.342  117.661500  117.661500  117.661500  117.661500
2017.01.04 10:24:57.921  117.661500      
2017.01.04 10:24:58.138  117.661000  117.661500  117.661000  117.661500
2017.01.04 10:24:58.360  117.661500      
2017.01.04 10:24:58.657  117.661500      
2017.01.04 10:24:58.774  117.661500      
2017.01.04 10:24:58.966  117.661500

I want to transform it like

                        Price  max             min  last price in second
2017.01.04 10:24:56  117.662500  117.662500  117.661000  117.661500
2017.01.04 10:24:57  117.661500  117.661500  117.661500  117.661500
2017.01.04 10:24:58  117.661000  117.661500  117.661000  117.661500

[dpb]

In what form do you have the data inside Matlab?

[Guillaume]

From the OP "I have a time series", I would assume it's stored as a timeseries.

[dpb]

That's one assumption; however, the way the question is posed it's not even clear for certain the OP has actually read the data into Matlab as yet...but if has as a timeseries object, then the data had to have been either an array or a series of vectors of equal lengths and therefore must have had some missing value.

Simply removing missing values and reformatting the date string should solve his problem essentially automagically...

I was guessing what he's showing is a text file on disk, not what he's entered into Matlab as yet...

What's really not clear is what the word mean has to do with anything.

Answer 1

[Peter Perkins]

I'm gonna assume that the thing displayed in the original post is not what you're starting with, that in fact you have something like a text file that looks like this:

Time, Price
2017.01.04 10:24:56.219, 117.662500
2017.01.04 10:24:56.300, 117.662000
2017.01.04 10:24:56.423, 117.661000
...

There are any number of ways to do this. If you have access to R2016b, use a timetable:

>> tt = table2timetable(readtable('prices.dat','Format','%{yyyy.MM.dd hh:mm:ss.SSS}D%f'))
tt =
              Time              Price  
    _______________________    ________
    2017.01.04 10:24:56.219    117.6625
    2017.01.04 10:24:56.300     117.662
    2017.01.04 10:24:56.423     117.661
    2017.01.04 10:24:56.492     117.661
    2017.01.04 10:24:56.554    117.6615
    2017.01.04 10:24:56.647    117.6615
    2017.01.04 10:24:56.750    117.6615
    2017.01.04 10:24:56.985    117.6615
    2017.01.04 10:24:57.342    117.6615
    2017.01.04 10:24:57.921    117.6615
    2017.01.04 10:24:58.138     117.661
    2017.01.04 10:24:58.360    117.6615
    2017.01.04 10:24:58.657    117.6615
    2017.01.04 10:24:58.774    117.6615
    2017.01.04 10:24:58.966    117.6615  
>> ttMin = retime(tt,'secondly','min'); ttMin.Properties.VariableNames{'Price'} = 'MinPrice';
>> ttMax =retime(tt,'secondly','max'); ttMax.Properties.VariableNames{'Price'} = 'MaxPrice';
>> ttLast = retime(tt,'secondly','lastvalue'); ttLast.Properties.VariableNames{'Price'} = 'LastPrice';
>> ttSecondly = [ttMin ttMax ttLast];
>> ttSecondly.Time.Format = 'yyyy.MM.dd hh:mm:ss'
ttSecondly =
            Time           MinPrice    MaxPrice    LastPrice
    ___________________    ________    ________    _________
    2017.01.04 10:24:56     117.661    117.6625    117.6615 
    2017.01.04 10:24:57    117.6615    117.6615    117.6615 
    2017.01.04 10:24:58     117.661    117.6615    117.6615

You can do the same things with a table, and varfun, by creating a grouping variable using dateshift to round the timestamps down to the previous whole second.