dde excel

[robcpettit]

Hi, Ive been playing with BA and its great. I have a question, its a bit long winded so please go easy on me. Im logging individual prices into excel. Then im linking col h into metastock to give me a chart. This works perfect, streems in great, keeps up, no strain on resources. Ofcourse though, theres a but. Because Im linking the whole of colomn h into metastock, until the cells get filled, the chart shows 0. This is a problem because if the first 5 values are 20 I get a line at 20 then a shrp drop to 0. This make rescalling impossible. So I tried limiting the link to h4:h20. Ounce all the cells are filled the 0 goes but ofcourse becomes a static chart. Then I tried putting a macro in worksheet_change so that when h20>0 it deleted row h4, thinking the next value would go into h20. the next value from ba went into h21 leaving h20 blank, therefore my macro would never get fired again. Ive tried cutting and pasting. If this makes sense does anybody have any suggestions.
Regards Robert

[Mitch]

I've never used a macro on a chart before so you'll have to fill in the itallic bit but how about... (I'm assuming the data starts at H4 from your post, and you want the graph to show the last 20 values once there are more than 20 items of data).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row < 24 Then Exit Sub
Application.EnableEvents = False
graph source = Range(Cells(Target.Row-19, 8 ), Cells(Target.Row, 8 ))
Application.EnableEvents = True
End Sub

[robcpettit]

mitch, thankyou for your reply. This has helped alot. Ive adapted the macro to:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Row < 121 Then Exit Sub
Application.EnableEvents = False
Range(Cells(Target.Row - 120, 1), Cells(Target.Row, 15)).Select
Selection.Copy
Sheet2.Select
ActiveSheet.Paste _
Destination:=ActiveSheet.Range("A1")
Application.CutCopyMode = False
Sheet1.Select
Application.EnableEvents = True
End Sub
As you can see Im taking the data from sheet1 and putting into sheet2 as I can then set my link to metastock as h4:h120. the only draw back, if using 1 sec refresh I have to wait 2 mins before data is added. Im now working to see if I can get data into the chart from first data point without 0's. Icouldnt use the graph source because I cant workout how to reference metastock. Im thinking that if there was a way to manipilate the dde link by altering the range its refering to through vba this would solve all. Thanks again.
Regards Robert

[kinsky]

hi rob,

can you please explain how you get data into metastock from excel?
thank you

[doris_day]

There was a guy who set up a site called Quantsports a while back and you could graph the data from Gruss in Ninjatrader, Worked really well but as there were very few that were interested he shut up shop. A shame really.

Personally I think TA isn't much help with trading but then again I'm a useless trader

[rourkem]

It would be really nice I guess to have the guys contact and hoped that this is still available.