Set the X Axis as the Time Axis of a Stock Chart

Hello!)

I have the following list of the OPEN, HI,LO,CLOSE prices and a column before them which shows the corresponding dates of the candles...

      03.10.2007  0.6933  0.6962  0.6931  0.6934
      04.10.2007  0.6933  0.6952  0.691  0.6932
      05.10.2007  0.6931  0.6942  0.6911  0.6921
      08.10.2007  0.6929  0.693  0.6897  0.6898
      09.10.2007  0.6899  0.6934  0.6893  0.6923
      10.10.2007  0.6922  0.6938  0.6905  0.6925
      11.10.2007  0.6926  0.6993  0.6924  0.6981
      12.10.2007  0.698  0.7006  0.6959  0.6963
      15.10.2007  0.6964  0.6987  0.6947  0.6957
      16.10.2007  0.6956  0.6976  0.6949  0.6972
      17.10.2007  0.6974  0.6985  0.6959  0.6964
      18.10.2007  0.6963  0.6993  0.6961  0.6993
      19.10.2007  0.6992  0.7001  0.6958  0.6969
      22.10.2007  0.6979  0.6994  0.6966  0.698
      23.10.2007  0.6979  0.6981  0.6944  0.6953
      24.10.2007  0.6952  0.6961  0.6936  0.6958
      25.10.2007  0.6959  0.6987  0.6955  0.6984
      26.10.2007  0.6983  0.7015  0.6978  0.7011
      29.10.2007  0.7014  0.7019  0.6986  0.6996
      30.10.2007  0.6994  0.6996  0.6964  0.698
      31.10.2007  0.6981  0.6983  0.6949  0.6956
      01.11.2007  0.6955  0.6962  0.6919  0.6941
      02.11.2007  0.6942  0.6964  0.6938  0.6941
      05.11.2007  0.6949  0.6963  0.6932  0.6956
      06.11.2007  0.6955  0.6978  0.6949  0.6975
      07.11.2007  0.6974  0.7002  0.6959  0.6967
      08.11.2007  0.6966  0.6981  0.695  0.6965
      09.11.2007  0.6964  0.7023  0.6958  0.702
      12.11.2007  0.7033  0.7078  0.7007  0.7071
      13.11.2007  0.7073  0.7078  0.7038  0.7049
      14.11.2007  0.705  0.7144  0.7042  0.7143
      15.11.2007  0.7142  0.7166  0.7121  0.715
      16.11.2007  0.7151  0.7171  0.7136  0.7136
      19.11.2007  0.7142  0.7163  0.7125  0.7155
      20.11.2007  0.7154  0.7182  0.7139  0.7177
      21.11.2007  0.7176  0.7212  0.7167  0.7197
      22.11.2007  0.7196  0.721  0.7176  0.7197
      23.11.2007  0.7198  0.7216  0.718  0.7192
      26.11.2007  0.7191  0.7195  0.7161  0.7179
      27.11.2007  0.718  0.7196  0.716  0.7172
      28.11.2007  0.7174  0.718  0.7121  0.713
      29.11.2007  0.7131  0.7164  0.7128  0.7151
      30.11.2007  0.715  0.7163  0.7109  0.7112
      03.12.2007  0.7123  0.7136  0.7088  0.7108
      04.12.2007  0.7107  0.7178  0.709  0.7168
      05.12.2007  0.7169  0.7238  0.7167  0.7204
      06.12.2007  0.7203  0.7224  0.7176  0.7219
      07.12.2007  0.7218  0.7231  0.7194  0.7218
      10.12.2007  0.7211  0.7212  0.7178  0.7193
      11.12.2007  0.7191  0.7214  0.7152  0.7199
      12.12.2007  0.7203  0.7207  0.7162  0.7189
      13.12.2007  0.7188  0.7208  0.7146  0.7164
      14.12.2007  0.7165  0.7176  0.7139  0.7151
      17.12.2007  0.7143  0.7156  0.7112  0.7129
      18.12.2007  0.7128  0.7158  0.7118  0.7154
      19.12.2007  0.7153  0.7205  0.713  0.72
      20.12.2007  0.7201  0.7238  0.7193  0.7216
      21.12.2007  0.7217  0.7255  0.7216  0.7248
      24.12.2007  0.7245  0.729  0.7242  0.7285
      26.12.2007  0.7282  0.7311  0.7278  0.73
      27.12.2007  0.7301  0.7338  0.7276  0.7321
      28.12.2007  0.732  0.7387  0.7307  0.7373
      31.12.2007  0.7368  0.738  0.7318  0.7356
      02.01.2008  0.7389  0.7449  0.7375  0.743
      03.01.2008  0.7429  0.7483  0.7416  0.7473
      04.01.2008  0.7474  0.7491  0.7424  0.7468
      07.01.2008  0.7471  0.7481  0.744  0.746
      08.01.2008  0.7461  0.7466  0.742  0.7454
      09.01.2008  0.7456  0.7502  0.7447  0.7487
      10.01.2008  0.7488  0.7565  0.7463  0.7548
      11.01.2008  0.7547  0.7585  0.7535  0.7551
      14.01.2008  0.7555  0.7611  0.7553  0.7611
      15.01.2008  0.761  0.7613  0.7526  0.7547
      16.01.2008  0.7548  0.7574  0.7439  0.7467
      17.01.2008  0.7466  0.7481  0.7415  0.7424
      18.01.2008  0.7425  0.7495  0.7418  0.7476
      21.01.2008  0.7457  0.7462  0.7421  0.7436
      22.01.2008  0.7435  0.7476  0.7396  0.7472
      23.01.2008  0.7471  0.7486  0.744  0.7478
      24.01.2008  0.7476  0.7489  0.7459  0.747
      25.01.2008  0.7468  0.7478  0.7399  0.7402
      28.01.2008  0.7406  0.7458  0.7391  0.7449
      29.01.2008  0.745  0.7451  0.7416  0.7426
      30.01.2008  0.7425  0.7482  0.7413  0.7472
      31.01.2008  0.7473  0.7492  0.7445  0.7468
      01.02.2008  0.7469  0.7543  0.7459  0.7531
      04.02.2008  0.7532  0.7543  0.7487  0.7512
      05.02.2008  0.751  0.7516  0.7444  0.7458
      06.02.2008  0.7459  0.748  0.744  0.7453
      07.02.2008  0.7454  0.7502  0.7444  0.7457
      08.02.2008  0.7459  0.7466  0.7424  0.7452
      11.02.2008  0.7461  0.7508  0.7437  0.7442
      12.02.2008  0.7441  0.7462  0.743  0.744
      13.02.2008  0.7441  0.7449  0.7413  0.7423
      14.02.2008  0.7422  0.7438  0.7403  0.7436
      15.02.2008  0.7437  0.7491  0.7429  0.7485
      18.02.2008  0.7495  0.7521  0.7478  0.7507
      19.02.2008  0.7506  0.7569  0.7506  0.7558
      20.02.2008  0.7557  0.7579  0.7531  0.7575
      21.02.2008  0.7576  0.758  0.7518  0.7547
      22.02.2008  0.7548  0.7552  0.7522  0.7537
      25.02.2008  0.7534  0.7554  0.7524  0.7539
      26.02.2008  0.7538  0.7565  0.7514  0.755
      27.02.2008  0.7551  0.7638  0.7536  0.7625
      28.02.2008  0.7626  0.7642  0.7609  0.7635
      29.02.2008  0.7634  0.7676  0.7625  0.763
      03.03.2008  0.7655  0.7676  0.7633  0.7661
      04.03.2008  0.7662  0.7673  0.7637  0.7658
      05.03.2008  0.7659  0.7688  0.7646  0.7665
      06.03.2008  0.7666  0.7692  0.7636  0.7653
      07.03.2008  0.7652  0.7657  0.76  0.7625
      10.03.2008  0.7622  0.7654  0.7595  0.7642
      11.03.2008  0.7641  0.7678  0.7634  0.7645
      12.03.2008  0.7646  0.7679  0.7626  0.7664
      13.03.2008  0.7663  0.7694  0.7643  0.7681
      14.03.2008  0.7682  0.7763  0.7664  0.776
      17.03.2008  0.7787  0.7911  0.7783  0.7871
      18.03.2008  0.7869  0.7884  0.7769  0.7785
      19.03.2008  0.7784  0.7893  0.7777  0.7876
      20.03.2008  0.7877  0.7885  0.7759  0.7777
      21.03.2008  0.7778  0.7801  0.7766  0.7785
      24.03.2008  0.7781  0.7791  0.7744  0.7759
      25.03.2008  0.776  0.7828  0.7751  0.7799
      26.03.2008  0.7798  0.7894  0.778  0.7888
      27.03.2008  0.7887  0.7899  0.781  0.7876
      28.03.2008  0.7878  0.7929  0.7859  0.7918
      31.03.2008  0.792  0.7981  0.7897  0.7947
      01.04.2008  0.7948  0.7958  0.7871  0.7901
      02.04.2008  0.79  0.7902  0.7852  0.7887
      03.04.2008  0.7888  0.7909  0.7822  0.7856
      04.04.2008  0.7857  0.7895  0.7841  0.7894
      07.04.2008  0.7888  0.7915  0.7867  0.7897
      08.04.2008  0.7898  0.7989  0.7895  0.7978
      09.04.2008  0.7977  0.8023  0.7967  0.8016
      10.04.2008  0.8015  0.8032  0.7967  0.7984
      11.04.2008  0.7983  0.8037  0.7977  0.803
      14.04.2008  0.7987  0.8012  0.7961  0.8011
      15.04.2008  0.801  0.8063  0.8007  0.8042
      16.04.2008  0.8044  0.8098  0.8024  0.8081
      17.04.2008  0.8082  0.8096  0.797  0.7986
      18.04.2008  0.7987  0.7994  0.7875  0.7916
      21.04.2008  0.7913  0.8042  0.7897  0.8037

I am using the Candle function to plot the above prices after they are imported from Excel. I wonder how do I set the X axis in such a way so that it shows the dates on major ticks based on the first date in the first column above (03.10.2007)?

Thanks!

D

Answer 1

[Walter Roberson]

Let DateColumnCell be a cell array of strings containing the various dates you give above. Then,

XD = datenum(DateColumnCell,'DD.MM.YYYY');
plot using XD as the X axis
datetick('x','DD.MM.YYYY')

[Dima]

thank you for your answer....do I need to enter the XD into the arguments of the datetick function? right now I have the followign vector of the dates in Matlab:

733325

733326

733327

733330

733331

733332

733333

733334

733337

733338

733339

733340

733341

733344

733345

733346

733347

733348

733351

733352

733353

733354

733355

733358

733359

733360

733361

733362

733365

733366

733367

733368

733369

733372

733373

733374

733375

733376

733379

733380

733381

733382

733383

733386

733387

733388

733389

733390

733393

733394

733395

733396

733397

733400

733402

733403

733404

733407

733409

733410

733411

733414

733415

733416

733417

733418

733421

733422

733423

733424

733425

733428

733429

733430

and the original plot has the usual number in the ascending order...from 1 to 600...I am at a loss at how to make those above dates appear on the axis in the ordered fashion...how do you think this can be done??)

D

[Fangjun Jiang]

No, you don't need to do that. Since XD is already in datenum format, datetick will convert it into datestr().

BTW, please just paste a snip of your data. As long as the data is sufficient to make the point, it's good enough.

[Walter Roberson]

candle() has an unusual calling sequence, which I did not know when I replied earlier.

Do not call datetick() yourself. Instead, call

XD = datenum(DateColumnCell,'DD.MM.YYYY');

candle(HighPrices, LowPrices, ClosePrices, OpenPrices,

Color, XD, 'DD.MM.YYYY')

[Dima]

thanks....please see the code below...I tried candle with the XD and it does not plot anything((

Answer 2

[Dima]

thank you very much for your replies...here is teh original code that imports the data from the testg.xls file:

[Num,Txt,Raw]=xlsread('testg.xls');
colors = {'r', 'b', 'g'};  %red, black, green
linetypes = {'-', ':'};   %dashed, solid
figure(1);hold on;
% grid;
% date = Num(6:end,56);
% matlabdate= x2mdate(date);
% datem=datestr(matlabdate);
% XD = datenum(date,'DD.MM.YYYY');
% candle(HighPrices, LowPrices, ClosePrices, OpenPrices,
% Color, XD, 'DD.MM.YYYY') 
candle(Num(7:end,3),Num(7:end,4),Num(7:end,5),Num(7:end,2),'k'); %,matlabdate,'DD.MM.YYYY');

The excel dates are placed in the BD column of the first sheet, the B TO E columns contain the prices....I am quite confused on how to make the Matlab show the correct dates on the X axis...as you will see above, this code converts the dates into matlab understandable format and then into the string.....but how do I use that with the Candle function remains hidden to me....can you please clarify?

I have uploaded the excel file here:

http://ifile.it/zn2eowi

Thanks!

D

[Walter Roberson]

What is x2mdate ?

Warning: "date" is the name of a MATLAB function. Naming a variable "date" is likely to lead to problems.

Unfortunately I do not have the toolkit that has candle() in it, so I cannot try the code myself.

[Dima]

I see...thanks...x2mdate is the function I found online which transforms excel serial date format into matlab...does the Candle function accept vector of the serial dates used for matlab followed by 'DD.MM.YYYY' argument?? thanks!

[Walter Roberson]

Is the date a string or numeric? If it is a string with the periods you showed before, then datenum(date,'DD.MM.YYYY') will convert to MATLAB serial date. If the date is numeric in excel's numeric date format, then x2mdate() is probably appropriate.

Candle does accept a vector of serial date numbers.

[Dima]

The date is numeric serial output of Excel in the following format

39422.00

39423.00

39426.00

39427.00

39428.00

39429.00

39430.00

39433.00

39434.00

39435.00

39436.00

39437.00

39440.00

39442.00

39443.00

39444.00

Do I just "plug it into" the Candle function and set the 'DD.MM.YYYY' as the output mode of the X ticks?

Thanks!

D

[Walter Roberson]

If it is numeric, then pass the x2mdate() converted version of it as the dates parameter, and pass the string representing the date format you want to appear on the x axis as the last parameter.

[Dima]

yes thanks...I use the following code to pass the value that you refer to:

date = Num(6:end,56);

matlabdate= x2mdate(date);

datem=datestr(matlabdate);

candle(Num(7:end,3),Num(7:end,4),Num(7:end,5),Num(7:end,2),'k',matlabdate,'DD.MM.YYYY');

and for some reason it just plots a blank chart with x and y axes starting from 0 and going to 1....not sure why....

[Walter Roberson]

It _looks_ like it should be okay, but I might be misunderstanding the Dates parameter. As I test I would do,

candle(Num(7:end,3),Num(7:end,4),Num(7:end,5),Num(7:end,2),'k',1:length(matlabdate),'DD.MM.YYYY');

and see whether you get date labels such as 01.01.0000 and 07.01.0000 . There is a possibility that the values that are passed in there are to represent the tick _positions_ at which a label is to be generated, but without obvious ability to change the x axis itself to anything other than 1 to the number of data points. If so that would seem to be relatively useless.

If I had the toolbox I would look at candle.m instead of making guesses about this stuff.

[Walter Roberson]

But before the test mentioned above, I would try using candle() exactly the way you show above, generating the apparent blank chart, and then right afterwards, command

xlim([matlabdate(1)-1, matlabdate(end)+1])

and see if the plot appears.

[Dima]

thank you very much for this help....I updated the above code a little, it had previously told me that the date numbers do not match:

%%

[Num,Txt,Raw]=xlsread('testg.xls');

colors = {'r', 'b', 'g'}; %red, black, green

linetypes = {'-', ':'}; %dashed, solid

figure(1);hold on;

grid;

dates = Num(6:900,56);

matlabdate= x2mdate(dates);

datem=datestr(matlabdate);

% XD = datenum(date,'DD.MM.YYYY');

% candle(HighPrices, LowPrices, ClosePrices, OpenPrices,

% Color, XD, 'DD.MM.YYYY')

candle(Num(6:900,3),Num(6:900,4),Num(6:900,5),Num(6:900,2),'k',matlabdate,'DD.MM.YYYY');

it does plot the candlestick chart with the dates on the x axis now...here is the snapshot

http://imageshack.us/photo/my-images/835/dateticks.png/

I have two more questions if you allow)

1)...one thing I would like to change is to have more date ticks on the X axis instead of only three as you see on that picture.....

2)I have one more question...there is a "10^5" label in the right hand corner of the chart, must be because the original serial date x values are high - I wonder how do I remove it...

Thanks for your help!

I love this place!)

D

Answer 3

[Dima]

I also had a separate question.....I wonder if it possible to assign a vector of the textual date ticks (taken from the serial date column imported from excel) to the already existing x axis that runs from o to 1000.

that would help a lot as I have some code that operates on the 0-1000 x axis labels to reduce the chart to show only the relevant price action.

THANKS!!!