How to extract every candles data to Excel?

• 04/11/2019 at 7:35 PM #96166
  jebus89

  So im making a tool that analyses my systems, but i need the underlying market data, so for example i would love to see the open, close, high, low of every candle in the chart. (on any timeframe)

  Could i perhaps make a system that trades on every candle to get that info?
  Any thoughts?

  

  jebus89

  Participant

  Topics: 118

  Replies: 586

  Been thanked: 238 times
  04/11/2019 at 7:50 PM #96167
  Vonasi

  Perhaps I am being stupid but can’t we get the OHLC of any candle on a chart very easily already?

  open[x] high[x] low[x] close[x]

  1 2 3 4

  open[x] high[x] low[x] close[x]

   

  

  Vonasi

  Moderator

  Topics: 192

  Replies: 5088

  Been thanked: 1810 times
  04/11/2019 at 8:00 PM #96169
  jebus89

  maybe im the stupid one but how would i use that?

  I would love a way to extract the high, low, open, close of every candle to excel.

  i was thinking that if i could “buy the open” and hold thru the candle then buy the next candle and so on, i could see the high/low with the mfe/mae on the detailed report but i guess thats faulty thinking anyways because it wouldnt say what the price is but rather the +/- of pips, which might help but i dunno..

  

  jebus89

  Participant

  Topics: 118

  Replies: 586

  Been thanked: 238 times
  04/11/2019 at 9:43 PM #96176
  Vonasi

  jebus89 wrote:

  excel

  The critical missing bit of information from your topic title and first post! I’ve edited your title.

  

  Vonasi

  Moderator

  Topics: 192

  Replies: 5088

  Been thanked: 1810 times
  04/11/2019 at 10:20 PM #96183
  robertogozzi

  There’s no way to export candles’data. Just use a pen and plenty of paper!

  I don’t think any broker would let anyone do this, maybe some make that data avilable if you pay a fee.

   

  

  robertogozzi

  Moderator

  Topics: 69

  Replies: 16466

  Been thanked: 3641 times
  04/11/2019 at 10:38 PM #96187
  jebus89

  Well i guess i could calculate it if i could buy at opening and sell at close, using mfe and mae in detailed report, on every candle? Is this possible?

  Thanks for the edit Vonasi.

  

  jebus89

  Participant

  Topics: 118

  Replies: 586

  Been thanked: 238 times
  04/11/2019 at 11:38 PM #96190
  robertogozzi

  Use this code, then drag data from report to excel:

  DEFPARAM CumulateOrders = true SELL AT MARKET BUY AT MARKET

  1 2 3

  DEFPARAM CumulateOrders = true SELL AT MARKET BUY AT MARKET

   

  

  robertogozzi

  Moderator

  Topics: 69

  Replies: 16466

  Been thanked: 3641 times
  04/12/2019 at 7:26 AM #96197
  Nicolas

  Another solution would be to drawtext every OHLC on the chart, make a screenshot or a PDF of the screen and send it through an OCR software. I should try to code something 🙂

  

  Nicolas

  Keymaster

  Topics: 81

  Replies: 20828

  Been thanked: 4739 times
  04/12/2019 at 8:09 AM #96205
  Nicolas

  This code works only for daily timeframe:

  defparam drawonlastbaronly=true startDate = 20190101 once plotted=0 if date=startDate then startbar=barindex endif if date=today and not plotted then for i = 0 to barindex-startbar do iday=day[i] imonth=month[i] iyear=year[i] iopen=open[i] ihigh=high[i] ilow=low[i] iclose=close[i] y=i*0.1 drawtext("#iyear#.#imonth#.#iday#;#iopen#;#ihigh#;#ilow#;#iclose#",barindex,y,dialog,standard,12) next plotted=1 endif return

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

  defparam drawonlastbaronly=true   startDate = 20190101   once plotted=0 if date=startDate then startbar=barindex endif if date=today and not plotted then for i = 0 to barindex-startbar do iday=day[i] imonth=month[i] iyear=year[i] iopen=open[i] ihigh=high[i] ilow=low[i] iclose=close[i] y=i*0.1 drawtext("#iyear#.#imonth#.#iday#;#iopen#;#ihigh#;#ilow#;#iclose#",barindex,y,dialog,standard,12) next plotted=1 endif   return

  Let’s try an OCR now..

  1 user thanked author for this post.

  Bard

  

  Nicolas

  Keymaster

  Topics: 81

  Replies: 20828

  Been thanked: 4739 times
  04/12/2019 at 8:19 AM #96209
  Nicolas

  OCR works, but the result is not yet perfect, should try black text on white background and/or with larger text. The thousand separator with comma is also annoying..

  I used this website: https://www.onlineocr.net/

  I was able to generate a txt file and imported it as text data into an Excel worksheet as data separated with semicolon.

  1 user thanked author for this post.

  GraHal

  

  Nicolas

  Keymaster

  Topics: 81

  Replies: 20828

  Been thanked: 4739 times
  04/12/2019 at 11:45 AM #96245
  jebus89

  Damn, was hoping there was some sort of way… oh well..

  Anyone got a good idea on how to calculate Beta (on ur system), which needs the underlying markets data?, without getting that data?

  

  jebus89

  Participant

  Topics: 118

  Replies: 586

  Been thanked: 238 times
  05/22/2020 at 10:59 AM #132795
  Bard

  Thanks Nicolas, okay so there is a kind of work around. A couple of thoughts:

  What is line 18 for?
  How would I derive the values for say, eg Ehler’s Universal Oscillator, a Zero Lag Average and a DPO or COG?
  I’m not clear how you lift the data in your image into a text file for the OCR website assuming there are more rows than a snapshot can take for the OCR s/ware?

  Cheers,

   

   

  

  Bard

  Participant

  Topics: 131

  Replies: 809

  Been thanked: 99 times
  05/22/2020 at 1:54 PM #132820
  Nicolas

  Y variable is the price coordinate.

  Bard wrote:

  How would I derive the values for say, eg Ehler’s Universal Oscillator, a Zero Lag Average and a DPO or COG?

  By learning how to use a loop 😉

  

  Nicolas

  Keymaster

  Topics: 81

  Replies: 20828

  Been thanked: 4739 times
  05/22/2020 at 3:40 PM #132850
  Bard

  I’m guessing a blank indicator (whether the background is white or black) isn’t right? Loops, yes I’m familiar with the concept!

  I tried an RSI but got a warning about a positive integer is needed for the RSI?
  Then I added Ehler’s Oscillator, my original Ehler’s indicator returns the “Result” of the oscillator and the 0 as “0.” I also have the bandedge added as a variable in the indicator.

  So its warning me: The function “Ehler’s Unic Osc SuperSmoother” called from “Get OHLC Data” returns 2 values but your code needs 1…. but I am not sure how to correct the syntax because I am already using an “=” sign straight after the “UnivOsc ” (pls see second line) but I need to use “indicator1, ignored = Call….”  and that’s two equals signs in one line?

  Cheers again,

  defparam drawonlastbaronly=true //UnivOsc = indicator1, ignored = Call "Ehler's Univ Osc SuperSmoother" UnivOsc = Call "Ehler's Univ Osc SuperSmoother"[25] startDate = 20190522 once plotted=0 if date=startDate then startbar=barindex endif if date=today and not plotted then for i = 0 to barindex-startbar do iday=day[i] imonth=month[i] iyear=year[i] iopen=open[i] ihigh=high[i] ilow=low[i] iclose=close[i] iRSI = RSI[i] iUnivOsc = UnivOsc[i] y=i*0.1 drawtext("#iyear#.#imonth#.#iday#;#iopen#;#ihigh#;#ilow#;#iclose#;#iRSI#;#iUnivOsc#",barindex,y,dialog,standard,12) next plotted=1 endif return

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

  defparam drawonlastbaronly=true //UnivOsc = indicator1, ignored = Call "Ehler's Univ Osc SuperSmoother" UnivOsc = Call "Ehler's Univ Osc SuperSmoother"[25]   startDate = 20190522 once plotted=0 if date=startDate then startbar=barindex endif if date=today and not plotted then for i = 0 to barindex-startbar do iday=day[i] imonth=month[i] iyear=year[i] iopen=open[i] ihigh=high[i] ilow=low[i] iclose=close[i] iRSI = RSI[i] iUnivOsc = UnivOsc[i] y=i*0.1 drawtext("#iyear#.#imonth#.#iday#;#iopen#;#ihigh#;#ilow#;#iclose#;#iRSI#;#iUnivOsc#",barindex,y,dialog,standard,12) next plotted=1 endif return

  

  Bard

  Participant

  Topics: 131

  Replies: 809

  Been thanked: 99 times
  05/27/2020 at 6:22 PM #133490
  Bard

  I meant, the indicator — even in it’s original form — doesn’t produce anything, regardless of a white or black background?
  I have it set on daily £/$ candles.

  Any ideas? (Anything to avoid having to use Yahoo Finance O/H/L/C and formulate indicator values in Excel!). Cheers,

  

  Bard

  Participant

  Topics: 131

  Replies: 809

  Been thanked: 99 times
• Author
  Posts