preparing data from a DDE link for charting

Can you explain the technicalities of RTD. Is it an alternative standard to DDE?

maybe i was not completely clear, you can use DDE with the chart sample above because the changes to the series are made with looking at the time field that's coming in with the quote not the event of a changing quote/cell. i assume that you have such a field [tradetime or something] in your DDE feed too

now that's hopefully clear on to RTD

RTD is indeed an alternative standard for realtime data feeds into Excel and i heard very recently from someone with Microsoft Excel that they're going to fully support this standard into version 12 and up of Excel.

In simple words you create a server component that's serving the feed, and in Excel you add cell formulas to ask your data from the server, the great thing is Excel will stay totally responsive so any update is being fed through linked cells etc.. no need to catch events and all difficulties you had trying to make DDE work seemless. So the bulk of the work is in creating a server component that's being fed with the data and translating that to fields that Excel can use, but once done it will pay off in easy of work on the frontend (Excel).

i'll enclose some links of samples (there are not a whole lot out there)

title: Real-Time Data: Frequently Asked Questions
source: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlrtdfaq.asp

title: How To Create a RealTimeData Server for Excel 2002
source: http://support.microsoft.com/kb/285339/EN-US/

title: HOW TO: Set Up and Use the RTD Function in Excel 2002
source: http://support.microsoft.com/?kbid=289150

hope this helps a bit
bruintje

but now i'm reading you're using Excel 2000, no RTD available there so i probably made only the point of an incentive to upgrade once Excel 12 is there next year ;-)

as said the charts sample provides some nice workaround that could be of use in your situation and frees you from having to write code

I have looked at the quite in stuff, but I cannot see how it works!

If I had the VBA experience I would write it but I can't (yet)

The func spec is:

Put the DDE link in A1. At n interval seconds, copy and paste the value to the next available empty cell (assume A2). After x seconds, take the series, and calculate the open (a2) the close (a2+x/n seconds) and the max and minimum values. Paste these values in cells C1,D1,E1,F1. Clear the range and begin again. On the next cycle, the values are pasted to C2,D2,E2,F2. The file is saved after each pass.

Is this difficult?

regards

Steve

Steve, that shouldn't be to difficult, i'll hack something together with comments to see what happens and will post in an hour or 2 or sooner depending on how much time "worldy things" claim :-)
Brian

this is a sample of a way to do what according to the spec
it contains one module with this code, a bit large because i had to fake a feed
it all starts with the sub StartAll() which fires of 3 timers, 1 for the feed, one for the quote shift and one for the creation of a ohlc record that gets copied to a second sheet where a timestamp is added

source: www.traktiq.net/ee_temp/ee-xl21626835.xls

-----------------
Option Explicit

Public m_lngSeriesRow As Long
Public RunWhen As Double
Public Const cRunIntervalSeconds = 15         '15 seconds minute
Public Const cRunIntervalSeconds2 = 60        '60 seconds
Public Const cRunIntervalSeconds3 = 5         ' 5 seconds
Public Const cRunWhat = "CreateQuote"         'this copies the quote
Public Const cRunWhat2 = "CreateQuoteRecord"  'this creates a ohlc quote
Public Const cRunWhat3 = "RunFeed"          'this starts the feed

'for more info on the scheduling in excel check
'source: http://www.cpearson.com/excel/ontime.htm

Sub StartTimer()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
     schedule:=True
End Sub

Sub StartTimer2()

RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds2)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat2, _
     schedule:=True
End Sub

Public Sub StartFeed()

  RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds3)
  Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat3, _
     schedule:=True
 
End Sub

Sub StopTimer()
   On Error Resume Next
   Application.OnTime earliesttime:=0, _
       procedure:=cRunWhat, schedule:=False
End Sub

Sub StopTimer2()
   On Error Resume Next
   Application.OnTime earliesttime:=0, _
       procedure:=cRunWhat2, schedule:=False
End Sub

Sub StopFeed()
   On Error Resume Next
   Application.OnTime earliesttime:=0, _
       procedure:=cRunWhat3, schedule:=False
End Sub

Public Sub StartAll()
  '//set variables
  Worksheets("Data").Cells(2, 2) = 2
  m_lngSeriesRow = 1
 
  '// timers at work
  StartFeed           'for this sample we start the timers here
  StartTimer          'for this sample we start the timers here
  StartTimer2         'for this sample we start the timers here
End Sub

Public Sub StopAll()
  StopFeed           'for this sample we start the timers here
  StopTimer          'for this sample we start the timers here
  StopTimer2         'for this sample we start the timers here
End Sub

' run a random generator to change
' cell value as pseudo feed in cell A2

Public Sub RunFeed()
Dim N As Long

  Rnd -1
  Randomize 500 * Worksheets("Data").Cells(2, 1).Value
 
  Worksheets("Data").Cells(2, 1).Value = Rnd
  StartFeed ' restart the feed
End Sub

'get cell value, insert a row below and paste value
Private Sub CreateQuote()
 
  Worksheets("Data").Rows(3).Insert xlShiftDown  'insert a new row
  Worksheets("Data").Cells(2, 2) = Worksheets("Data").Cells(2, 2) + 1
  Worksheets("Data").Cells(3, 1) = Cells(2, 1)   'set value equal to quote
  StartTimer 'we need to restart the timer

End Sub

'create calculate open, high, low, close record
Private Sub CreateQuoteRecord()
 
   m_lngSeriesRow = m_lngSeriesRow + 1          'add 1 to our row counter for the chart data
   Worksheets("Data").Range("quoteRecord").Copy 'we copy our ohlc record to the chart sheet
   Worksheets("ChartData").Range("A" & m_lngSeriesRow) = Now() 'add a time
   Worksheets("ChartData").Range("B" & m_lngSeriesRow).PasteSpecial Paste:=xlPasteValues 'paste the record
   ThisWorkbook.Save
   Worksheets("Data").Cells(2, 2) = 3 ' reset counter for this record
   StartTimer2 'we need to restart the timer
   
End Sub
-----------------

hope this helps a bit
bruintje

Hi Brian

I appreciate your hard work, but it is getting late here in Europe. I will implement tomorow and report

Steve

that's ok Steve, we're on the same side of the ocean it's almost 0000 here in Amsterdam :)

Hi Brian

I have tried it using my own DDE link but it has thrown up some spurious data (that I think is to do with your random feed!) I only kicked off StartTimer and StartTimer2. Is there a way I can show you the result?

The other thing is thatit does not want to close. Even if I stop all the macros, it immediately re-opens

what's probably easiest with using your own links is commenting out the startfeed and stopfeed entries in the code. that way they are not started at all and the timers will only try to log the data that's coming in through the DDE link in the cell A2

EE is a bit limited on communications except for the threads here

mail is in my profile

reopening will occur if the timers are still running, will look into that and be back a bit later

The 'spurious data' is the current time. I have no idea why this is being created

It seems that for some reason, the current time instead of the DDE feed (I have replaced your random feed with the actual DDE link) is being entered.

It is probably easier to see what is going on with the actual DDE data coming in.

If you have the inclination, you can download the application (after registering)

http://www.forextradermentor.com/tmsignup.html

and run it in evaluation mode. You can then see the  View>DDE data out links to get the link. I am using TraderMetrics|DataOut!RawBid as the feed.

The other thing that I noticed is that their seems to be some relative cell references here. Can I replace them with named references and can I hide these sheets, so that the eventual chart page is the only one visible?

regards

Steve

wow, i fixed the timer problem which was a bug from my hand and changed the cell reference for the open field which was wrong the workbook download is updated www.traktiq.net/ee_temp/ee-xl21626835.xls

will now try to get the same setup as yours to see what's happening

Hi Brian

Definitely working better although 0 for initial open rate will screw the chart (IMHO). Still getting time instead of DDE link if I have focus on the chart sheet .

Hi Steve,

Seems to be working ok with the DDE link, but i can't find an entry for the time in the DDE links.

I've changed it so both data sheets are not visible after you start the app.
The chart is updating only the last 15 ticks from the chartdata sheet.
It still using the time of timer not the time you could get from the DDE link.
To stop you need to use tools > macro > macros > choose stopall > run

you can download a new version www.traktiq.net/ee_temp/ee-xl21626835.xls

just comment on how it works out

Brian

Hi Brian

I have run it from the TraderMetrics data and it is fine, apart from the fact that I cannot reset the data (that is, the data from you session is there at completely different rates.

I am confused to as to where the data sheet 'disappeared' to after I viewed the chart sheet. So, How do I stop it again?

Now you have got this far, perhaps for a couple of hundred points, you would provide the following functionality?

1)  Replicate the data in Excel  H-L-C format (which requires a different order for the columns), line data (based on closeing price)  or even make a toggle between the three..

2) The source data is sometimes 'compressed'. That is 1 minute 'realtime' represents n minuyes (up to a couple of days) in simulated data. So the graph x scale would show the compressed time, say each 'period' being 15 minutes in stead of 1 minute

3) The time scale for the OHLC/HLC is configurable (i.e. a OHLC at anything from 15 seconds to 1 day)

4)I work with Fibonacci retracements and extensions. This invoves setting 6 values and displaying as horisontal lines on the Y access.

5)The possibility to pre-populate the chart with data, so the 'live' session is showing a continuation of the data (i.e that being produced through the DDE link).

As you can see I have set 200 points to the total but let me know if you think it is worth more :-). Fabulous job so far which will help my students greatly.

regards

Steve

 

Hello Steve,

i'll work on this tomorrow and post a new workbook around the end of the day

Add the start and stop on the toolbar.
A cleanup routine for previous runs so every run will start with fresh data.

1. a toggle button for three kind of charts based on the same data will be added
2. ok, at this point each period is read from the input and it's not configured to be time or date just what happens to be the input have to see how this plays out
3. i think this is already possible as a feature of excel itself where it compresses the data itself
4. this one i like myself ;)
5. this will be another button on the toolbar that you can run the dde feed until it has enogh data to fill the chart and it stops untill its started again with the start button

Looks like work, but these kind of sheets are a personal favorite so the points are ok :)

Brian

Regarding setting the sample periods (so that nothing is misunderstood)

The period of the OHLC sample can be as low as 15 seconds (extreme frequency) up to daily OHLC and anywhere in between (normally 5 minute, 15 minute, hourly).

To simulate several days (or weeks/months/years), it is unrealsitic to have someone run a simulation for that length of time (as well as getting RTE 6 overflow errors!). So what I do is create a compressed time file, so that say, 1 hour is represented by 1 minute of real time data. I would like the time on the x graph to reflect this, but I admit that because TraderMetrics does not generate a DDE timestamp (unfortunately left out of the spec!), it would have to be set manually within the spreadsheet, overriding the 'normal' sampling time, or at least working in combination.

Hello Steve,

Just have uploaded what i'll have thus far.

There is setup page to start with
It contains a start and stop button (both done)
You'll find there a button to fill in the fibo levels, and type of chart (1 and 4)
The chart starts building with enough data (15 ticks in this case)

todo
The adding of the Fibo lines is not working yet
I will look at the timeframe setup tomorrow

I'll finish the todo and that's it for this question.

Follow up can be done in new questions, it's not about the points but more to keep the balance between work and a question on EE ;-) And at the end i'll post all code here so anyone can help in a follow up question.

Regards
Brian

I'm extremely impressed

Hi Steve,

A new version is uploaded.

You can prepopulate a chart with the first 15 ticks (minutes in this case)
Then you can use Startquotes to start the timers and the normal run
I add fibo levels automatically, but i guess you want them different or as a choice yes/no
The fibo levels are taken from the difference of the current move  --> (high-low)*fibo and added to the low. The high and low are for the current 15 tick period only.

I think the only thing that is not yet implemented is the timing ranges.

Let me know what you think

Best regards
Brian

btw. I've played a bit with the simulator while waiting for the data and its fun :-)

Hi Brian

I am not sure where I am prepopulating the chart data from, but in any case I got a message saying that Excel could not create the HLC chart. (I am giving you the errors as they occur so as to speed upm development! Unless you want me to close it here and start a new question)

Hi Steve

it's ok you can post here, till we got this one working
for the prepopulation i assumed the simulator application DDElink to be up
the only difference with a normal run is that it stops after it creates the chart based on 15 minutes

i've ran it on the DDE simulator for OHLC and the Line chart of course i forgot the HLC will check now

Brian

It is up. How can I get the resulting sheet back to you so you can review it?

I have tried to reset the sheet, but the values are still present.

If you like, I can register you (you go to the Help>registration information in TraderMetrics and give me the PIC code, and send you the same file that I am working on (i.e. a longer period)

forget the last comment on the clean up - when I reverted back to the sheet, the sheet had cleaned itself up!

cleanup is called on closing and reopening the sheet, i will change that to the setup button

Another thing re: Fibs

You have (amazingly!) set the values to the right levels. (How did you know this?)

I had the idea that I could put in the equivalent rates "on the fly". for example 0%=1.2095 61.8%=1.2116, 38.2%=1.2129,100%=1,2150 123.6%=1.2163 161.8%=1.2184,223.6%=1.2218
putting in those absolute rates and then plotting a horizontal line for the value across the y axis. When one fib signal passed, the numbers could be reset when the next howed up.

If I could, I would like to show you what that looks like on a pro charting application!

(When I opened the workbook to do the calculation there, I got a RTE 9 subscript out of range - I suspect because the wrong book was activated. That is not so favourable as my students will have to work with at least one other workbook))

currently i'm doing the CMT course trying to broaden my horizon and positioning myself to become a technical analyst in some time and that's were the fib levels come from :-)

the error i'll look into didn't test it that  way, and thats exactly what a test is for ;)

This is the data in the raw data sheet


feed            open      high      low      close      fibo1      fibo2      fibo3      fibo4      fibo5      fibo6
1.2222      2                              0      0      0      0      0      0
1.2222                                                                  
1.2222                                                                  
1.2222                                                                  
1.2217                                                                  
1.2221                                                                  
1.2229                                                                  
1.2235                                                                  
1.2244                                                                  


and this is the chart data

                              fibo1      fibo2      fibo3      fibo4      fibo5      fibo6
16/11/2005 20:58                              0      0      0      0      0      0
16/11/2005 20:58                              0      0      0      0      0      0
16/11/2005 20:57                              0      0      0      0      0      0
16/11/2005 20:57                              0      0      0      0      0      0
16/11/2005 20:56                              0      0      0      0      0      0

The fibs are set to:

1      1.2095
2      1.2188
3      1.215
4      1.2115
5      1.223
6      1.2245
      1

I must have set it up incorrectly, but this is what I see, and obviously no chart data. I guess I am missing something here.

I hope that this information helps more than it doesn't !

there is a new version posted that handles your latest post and the cleanup, i posted a wrong version in the afternoon which emptied out the formulas in the rawdata sheet

By the way, an example of the fibonacci chart as I would like to present the data (the diagonal line is not so important and I guess difficult to replicate) is here

www.forextradermentor.com/eetemp/fibsample.jpg

Hi Steve,

saw that there was indeed a problem with using this workbook in conjunction with another. thought that the thisworkbook reference was used everywhere but it wasn't. now this bug shouldn't be popping up anymore and the strange markers on the fibo levels are gone too in the latest upload (1038PM)

can you explain a bit more how you want the fibo levels being calculated?

at this point i calculate them by taking the difference between the high and low of the period and add them to the low, but i guess you have another idea looking at your latest chart?

i think the little bugs that you mentioned earlier are now gone,

i can add a counter to show the progress of the prepopulation because that takes a bit of time
next add a box on the config screen to change the periods between the ticks
and change a bit on the calculation of the fibo levels

but that should be it for this thread :-)

as said i'll post code and instruction at the end of this thread for others to reconstruct since this is EE and i can't guarantee to be available at all times besides there are some good excel gurus in this TA  to help you if needed

this is my last post for the day, so see you tomorrow

Brian

Hi

I got it working. I see you have actually done the calculations on the fib levels. I have a spreadsheet based calculator that does this, so despite your excellent efforts, what is required is just the input for 7 (not 6 - my mistake) fibonacci levels based on the user's calculation, so that he can see the entry and exit targets.

(The aim of the exercise is to train traders to enter, set stops and limit orders, using a file with several opportunities to plot trading signals in one session)

Therefore, the levels need to be adjusted 'on the fly', and color differentiated for ease of tracking.

Using the target % as you have done requires that the direction of the trend is known and the first and second (0% and 100%) are entered, although it would be self explanatory which direction is required if the 0% was higher or lower than the 100%.

For this exercise, I use the 0% and 100%, the 38.3% and 61.8% as retracements and the -23.6%, -61.8% and the -1.236% as the extensions.

Hope that makes it clearer, but thanks for the time dedicated to this.

Steve

Just to answer the other question. I cannot comment on the pre-population as I don't know exactly the philosphy, unless it is possible to run the simulation for n minutes to get the data, stop and save the workbook and then start running it with additional appended data at a later stage, This would be so that there was some chart history.

If that is so, I need to define the length of time of prepopulating, as well, of course, being able to adjust the sample time to creat variable period OHLC.

Steve

Hello Steve,

A new version is posted.

The setup for has 7 entries for fibo levels
It also has an entry for the number of prepopulation OHLC records needed to chart at first
And it has one box for the time between 2 ticks
And it has one box for the time needed to form a OHLC record

The fibo levels are drawn in a continous line of various colors

Hopefully this works better

Brian

Hi

Wll, it is working, but I got a run time error

(www.forextradermentor.com/eetemp/error1.jpg) which debugged to here:
(www.forextradermentor.com/eetemp/error2.jpg)

Perhaps because I was running in the browser?

Anyway, there are some puzzling things happening.

1) The fib series: I cannot see that this is so difficult to enter directly from the worksheet and why they have to be on a separate axis?
If I want to draw a horisontal line, I just create a column with the same values copied down to draw them as a series. This would be far simpler as I only need them as a visual reference and so they can be changed at any time

2) Why do I only see the last 6 entries on the graph?

(www.forextradermentor.com/eetemp/chart1.jpg

3)What about the graph scaling? It is showing the same tendencies that I put in a previous question (i.e Showing min:-1 and max:+1)  

4) Can you explain please how exactly to use the prepopulation?

Otherwise it is a great peice of work. If you can please have a look at the above, I will gladly sign this question off.

Steve

Hi Steve,

After the domestic duties i'll be looking into those points and write a small how-it-works

The entering of the fibo series is indeed not so difficult to do by hand, what i'll probably do is put them in a seperate sheet where they can be changed while the chart is being made. IT's more of a leftover of the first prototype which calculated the series on the fly.

Brian

Hello Steve,

the setup sheet (ready for download) contains now 3 small lists

- fibo levels
- chartttype
- timer settings

1. the fibo are now set as a constant series in their own sheet
2. you see as much bars as you prepopulate
3. not sure what you mean by this, the fibos are on their own axis because their charttype is line and the other is bar or ohlc those 2 bite each other on one axis
4. you start a sheet, then set the three config lists.

> 7 fibo levels

> set a 1 after the charttype you want to use other 2 should be blank

> the timer settings
prepopulate lenght (= number of bars to plot to start)
tick (= length of one tick in seconds)
quote bar (=lenght of bar before the next bar is calculated in seconds)

Next you use the prepopulate button to add the number of bars to start with/from
In the statusbar you see an indication of how far the progress of prepopulating is
Aftre the messagebox that its done
You can change the field for the prepopulation to set it longer or shorter
Then you click start timer and this will run the chart
Youcan use the stop timer button anytime to stop the application

Hope this is clear, sometimes i can write it easier in code then a simple helpfile

again could you explain what you mean by -1 and 1 for the axis? are you changing something manual or is that needed?

The new sheet is a bit larger then the previous one, this comes from the prepopulation i've done for the fibo levels to contain already 1000 rows. The application will only use as much as is displayed not all 1000 so you can easily delete some rows.

off for now

Brian

PS there was a bug in the prepopulation that would erase the prepopulation when you chose to start the normal run. Just to let you that there was something wrong indeed :)

I have run the latest version and still seem to have a chart scaling problem. Please see the screen shot

www.forextradermentor.com/eetemp/scaling.jpg

Hello Steve,

What i did was

- download the file to disk
- started the tradermetrics app
- started the preload on the excel sheet
- after 10 bars it gave me the message it was ready
- then i started the start quotes
- and still the chart did look ok with me

Could you describe how you got into this bug?

Brian

I will do exactly what you did and let you know (although I am sure I did exactly that!)

did you fill in the fibonacci retrace levels?  i think that is what you see empty levels, i'll fix that first

a new version is uploaded, you can set a field if you want to use the fibo levels yes or no

btw i use 1 or an empty field for the choice fields like the charttype and the use fibo levels

So far, so good, but I am not running on Fibonacci numbers-further testing being done!

OK, I have used the settings as shown with Fibs and the graph screenshot shows the result with the unadjusted scale.

www.forextradermentor.com/eetemp/exhibitA.jpg

www.forextradermentor.com/eetemp/exhibitB.jpg

As you can see, the fib scale is 'off'