Convert CSV data for use with CandleStickChart

can anyone show me an example of how to import a CSV file and produce and CandlestickcChart using the imported data.

I have tried

data = Import["book1.csv"] // Dataset


The data is in the following format:

Date    Open    High    Low Close   Volume
15/10/10 0:00   1.40237 1.40294 1.40165 1.40284 177.0999994
15/10/10 0:05   1.4029  1.4029  1.40133 1.4017  141.7000002
15/10/10 0:10   1.4016  1.40218 1.40059 1.40206 204.0000004
15/10/10 0:15   1.40202 1.40229 1.4014  1.40194 171.8000001
15/10/10 0:20   1.402   1.40235 1.40177 1.40191 122.9000005


and then CandleStickChart[data] which produces the following error:

CandlestickChart::ldata: {{Date,Open,High,Low,Close,Volume},<<49>>,<<451>>} is not a valid dataset or list of datasets.

I'm unsure as to what I need to do to create valid Dataset. I'm using version 11.3

Update

Here is an example of the CSV file:

Here's a link to file https://1drv.ms/u/s!AhaccQOwpczgpqEnY-HoQR2nTFAcHw

Date,Open,High,Low,Close,Volume
15/10/10 0:00,1.40237,1.40294,1.40165,1.40284,177.0999994
15/10/10 0:05,1.4029,1.4029,1.40133,1.4017,141.7000002
15/10/10 0:10,1.4016,1.40218,1.40059,1.40206,204.0000004
15/10/10 0:15,1.40202,1.40229,1.4014,1.40194,171.8000001
15/10/10 0:20,1.402,1.40235,1.40177,1.40191,122.9000005
15/10/10 0:25,1.40206,1.40229,1.40141,1.40169,199.4999995
15/10/10 0:30,1.40179,1.40231,1.40111,1.40199,190.0000001
15/10/10 0:35,1.40202,1.40214,1.40127,1.40174,127.0000005
15/10/10 0:40,1.40177,1.40178,1.40116,1.40163,145.6
15/10/10 0:45,1.40167,1.40242,1.40157,1.40218,160.5000002
15/10/10 0:50,1.40207,1.40253,1.40181,1.40234,335.3000003
15/10/10 0:55,1.40243,1.40354,1.40195,1.40347,156.6999996
15/10/10 1:00,1.40345,1.40399,1.40323,1.40354,226.3000011
15/10/10 1:05,1.40343,1.40429,1.40328,1.40411,229.8000007

• Can you put a link to "book1.csv" (or a sample of it)? – Fraccalo Jun 6 '18 at 10:54
• Did you look at the documentation of CandlestickChart? It tells you exactly what format it expects… To get you started: Look at the output of {#,{#2,#3,#4,#5}}&@@@Rest@data – Lukas Lang Jun 6 '18 at 11:08
• yes, I read the documentation where it asks for a pure date and OHLC pair, I will reread. Thanks for the pointer, I have looked at the output. Hopefully fixing the date format will resolve my problem. Thanks – Beaches Jun 6 '18 at 11:21
• Welcome to Mathematica.SE! I suggest the following: 1) As you receive help, try to give it too, by answering questions in your area of expertise. 2) Take the tour and check the faqs! 3) When you see good questions and answers, vote them up by clicking the gray triangles, because the credibility of the system is based on the reputation gained by users sharing their knowledge. Also, please remember to accept the answer, if any, that solves your problem, by clicking the checkmark sign! – halirutan Jun 6 '18 at 12:29
• @Beaches You can edit your own question to add new information. Don't make them as an answer. Answers are really meant to be answers. I edited your post and included the information. – halirutan Jun 6 '18 at 12:31

To clean up @Fraccalo's answer a bit:

(* Drop the first row as a header, and Import all but the last columns (Improved in 11.3) *)
data = Import["~/Desktop/test.csv", {"Data", All, 1 ;; -2}, "HeaderLines" -> 1]

CandlestickChart[
(* CandlestickChart takes a date, then a list of open high low close,
so map over each row, interpret the first column as a DateTime
and group the remaining 4 columns. *)
{Interpreter["DateTime"][First[#]], Rest[#]} & /@ data
]


I've noticed Interpreter can be a bit slow, so this is a slightly faster approach. Turns out this seems to work, but issue a message:

DateObject["15/10/10 0:10"]


So it's possibly less reliable, but this does produce the same results for the data given above:

CandlestickChart[
{Quiet[DateObject[First[#]]], Rest[#]} & /@ data
]


Try this:

a = Import["book1.csv"];
aPlot = {DateObject[{#[[1]], {"Day", "/", "Month", "/", "Year", " ",
"Hour", ":", "Minute"}}], #[[2 ;; -2]]} & /@ a[[2 ;;]];
CandlestickChart[aPlot]


Note that I'm ignoring the Volume data, as the CandlestickChart says that ChandlestickChart takes data in the form:

{{Subscript[date, 1],{Subscript[open, 1],Subscript[high, 1],Subscript[low, 1],Subscript[close, 1]}}


I'm not sure it's exactly what you are looking for (and I'm not familiar with CandlestickChart), but you can try this as a starting point for doing what you need to do.

• Thanks, this has got be going. – Beaches Jun 8 '18 at 10:14