Draw candlestick chart by reading the data in excel file.

I need some help, i downloaded some data in excel format. Any idea to draw a candlestick chart by reading the excel file?

Attached file is the example of excel data file. First to the following column of the excel file represent (Date,Time,Open,High,Low,Close,Volume).

Any idea using this information and data to draw a candlestick chart?

Answer 1

[Stephan]

Hi,

you can use the following code:

Data = readtable('DAT_MT_EURAUD_M1_2018.csv');
Data = mergevars(Data,[1 2]);
Data.Var1 = join(Data{:,1});
Data.Var1 = datetime(Data.Var1,'InputFormat','yyyy.MM.dd HH:mm');
Data.Properties.VariableNames = {'Date_Time', 'Open', 'High', 'Low',...
    'Close', 'Volume'};
Data = table2timetable(Data);
candle(Data)

It takes a little moment to execute, since this is a pretty big dataset. But it is working.

Best regards

Stephan

[Tai Wai ming]

Thank you stephan, i will try it on my friend laptop. Seems like my laptop take a very long moment and still cant run the result out.

[Tai Wai ming]

Hi stephan, Sorry for late reply. I had tried run the big set of data but it seems like the data is too big, it run for many hours and still result not yet shown. So i try to cut short the excel data to 100 row only and the result seems like not a candlestick chart.

[Stephan]

please save the data you used in a .mat-file and attach it. I tried with a small part of your original data and it worked well for me.

[Tai Wai ming]

Hi stephan, can i know how to convert the excel file to .mat-file?

[Stephan]

use the save workspace button in the home tab of Matlab.

[Tai Wai ming]

i have try to convert the csv file to .mat-file and i get the error. Any idea?

[Stephan]

readtable make no sense for .mat-files. this is useful for .csv-files. if you have the data in Matlab workspace you can save it as a .mat-file

[madhan ravi]

What Stephen meant was to attach your data here so that it could be tested out.

[Tai Wai ming]

Sorry for the missunderstanding. Here is the excel file in csv that i tried with 100 row only.

[Stephan]

Which Release do you use? On R2018b it works for me (scroll right):

I attached the .mat-File of your tetsting data. If you load it, it should be possible to get the plot with:

load('C:\YOUR FOLDER NAME\data.mat')
candle(Data)

since all the other lines are just for creating a timetable. Does this work for you? For further help it is important to know which release you work with.

[Tai Wai ming]

I using R2018a release. Maybe i try to upgrade my matlab to 201b and try it out.

I tried the above code load the .mat data and the same result came out also.

[Stephan]

the same result means the correct result?

If yes then you should run the code on 2018a with your .csv-file and compare the resulting timetable with my resulting table. maybe there is an issue due to the different release.

If you have the possibilty to upgrade to 2018b the problem should be solved anyway.

if my answer was helpful for you dont miss to accept it.

[Tai Wai ming]

i mean although i use .mat-file, the result is not correct. So i will try on R2018b after this

[Stephan]

?then it will work

[Tai Wai ming]

Yes stephan for small set of data i try it on R2018B it work! thank you so much. But when i try to run on big set of data it prompt out that what memory error i cant screenshoot the error due to matlab crash after that. Any idea on this error?

[Stephan]

i would suggest that you ask yourself how useful a 1-Minute Resolution may be for such a big time span.

[Tai Wai ming]

Okay, Thanks for the help stephan i will consider about it. Thank you so much