Import date, time together with candlestick data for Candlestick chart

Hello all. I want to import historical data in Matlab and later plot Candlestick chart. There are several examples available on the net which they have an only date (daily time frame). When I want to load time as well, then Matlab cannot read in the time as well. I also tried to import from the Financial Toolbox, but the same problem. How can I import date and time together and plot a candlestick chart?! (Please consider the attached file).

In addition, is there any built-in function to convert for example 15 minutes timeframe (M15) to an hourly time frame (H1)?

Answer 1

[Peter Perkins]

Your file has hundreds of empty lines in it, Ive removed them. Try this:

>> t = readtable('EURCADecn15.csv');
>> t.DateTime = datetime(t.Date,'InputFormat','yyyy.MM.dd','Format','dd-MMM-yyyy HH:mm:ss') + duration(t.Time,'Format','hh:mm');
>> tt = table2timetable(t(:,3:end));
>> head(tt)
ans =
  8×5 timetable
          DateTime           Open      High      Low      Close     Volume
    ____________________    ______    ______    ______    ______    ______
    18-Jun-2018 12:00:00    1.5266    1.5282    1.5262    1.5278     1710 
    18-Jun-2018 12:15:00    1.5278    1.5285    1.5274     1.528     1619 
    18-Jun-2018 12:30:00    1.5279    1.5281    1.5272    1.5273     1485 
    18-Jun-2018 12:45:00    1.5273    1.5274    1.5262    1.5269     1374 
    18-Jun-2018 13:00:00     1.527    1.5282    1.5261     1.528     1629 
    18-Jun-2018 13:15:00     1.528    1.5287    1.5276    1.5285     1513 
    18-Jun-2018 13:30:00    1.5285    1.5297    1.5284    1.5293     1368 
    18-Jun-2018 13:45:00    1.5293    1.5299    1.5287    1.5294     1451

Beginning in R2018a, in many cases readtable would have created Date and Time as a datetime and duration vector, but your file has formats that readtable doesn't automatically recognize (and in fact I had to assume that the time was hh:mm, not mm:ss) -- you can use detectimportoptions, though.

The conversion to hour;y is also easy:

>> tt = retime(tt,'hourly','mean');
>> head(tt)
ans =
  8×5 timetable
          DateTime           Open      High      Low      Close     Volume
    ____________________    ______    ______    ______    ______    ______
    18-Jun-2018 12:00:00    1.5273    1.5274    1.5262    1.5269     1374 
    18-Jun-2018 13:00:00    1.5293    1.5299    1.5287    1.5294     1451 
    18-Jun-2018 14:00:00    1.5308    1.5311      1.53    1.5302     1455 
    18-Jun-2018 15:00:00    1.5315    1.5323     1.531    1.5311     1863 
    18-Jun-2018 16:00:00    1.5298    1.5307    1.5296    1.5306     1920 
    18-Jun-2018 17:00:00    1.5314    1.5341    1.5313    1.5339     2330 
    18-Jun-2018 18:00:00    1.5349    1.5357    1.5342    1.5353     1328 
    18-Jun-2018 19:00:00    1.5354     1.536    1.5352    1.5353      858

but you'll need to decide how to do it -- max, last, mean, ... ?