repost: Need Help With Steaming Data Macro

[rnrss]

Hello everyone...

I am trying to capture the highest high and lowest low in streamin
data. What I have done is not working correctly and I am at a loss ho
to correct it, here are the results:

I am using this:

If bid < oldlo Then
lo = bid
End If
oldlo = lo

If ask > oldhi Then
hi = ask
End If
oldhi = hi


I thought this would capture and latch the highest Hi value and lowes
low of the stream and increment each time a new hi was or lo wa
reached.

Instead of latching it, it enters the data request string
<=edemo|tik!id558?ask> into the cell and it does not latch the data a
I expected it should, it enters the ask string rather than a value...
dont think it is possible to latch anything in the spreadsheet and t
the best of my knowledge vb is the only way to accomplish this.

I am very lost on how to handle data in this manner.

Does anyone have an idea how I can get streaming data to latch an
paint an output to a cell that can be further operated on once i
excel?

a snipped version of the code is below...

Dim bid As String
Dim ask As String
Dim hi As String
Dim lo As String
Dim oldhi As String
Dim oldlo As String

bid = server & topic & id & "bid"
ask = server & topic & id & "ask"

'HOD and LOD
If bid < oldlo Then
lo = bid
End If
oldlo = lo

If ask > oldhi Then
hi = ask
End If
oldhi = hi

ActiveCell.offset(0, 2).Value = bid
ActiveCell.offset(0, 3).Value = ask
ActiveCell.offset(0, 7).Value = hi
ActiveCell.offset(0, 8).Value = lo




Here is another update:

tried this and here are the results:

cell p8 is <=edemo13|tik!id0?bid>
cell p9 is <=edemo13|tik!id0?ask>

in excel in cell 21 i entered <=p8> the cell for the bid and same wit
the ask where cell 22 <=q8>

then I told vb to get the values and rewrite them into cells 23 and 2
respectively just to see if it would work and I get a type mismatc
error...

bid1 = ActiveCell.offset(0, 21).Value
ask1 = ActiveCell.offset(0, 22).Value
ActiveCell.offset(0, 23).Value = bid1
ActiveCell.offset(0, 24).Value = ask1

Everything I try doesnt work..

 

[RWN]

Your "oldlo" will be updated with every iteration (ditto "oldhi").
IF bid<oldlo then oldlo=bid
IF ask>oldhi then oldhi=ask

If you need "lo"/"hi" then put them inside their respective IF statements.

 

[rnrss]

Thanks for the reply but that does not work... it still updates ever
iteration the way you suggest too... That is why I am pulliong m
hair out lol
rn

 

[RWN]

Given the snippet you've provided I can't see how it wouldn't work, so there's got to
something else at play.

Are oldlo & oldhi declared ("Dim oldhi as....") in the declarations section?
If they are declared in the routine (or never declared) and you leave the routine between
iterations then oldhi/oldlo will always be zero when it makes the comparison (and,
therefore, always be updated).

 

[rnrss]

Hello thanks again for your response...

Here is a pic of what it looks like:
http://i22.photobucket.com/albums/b321/rnrss1/LatchIssue.jpg

Here is the complete program:

Dim genId As Integer

' I tried Dims here
' Dim myhi As String
' Dim mylo As String
' Dim mybid As String
' Dim myask As String

Sub requestMarketData()

' contract description vars
Dim symbol As String
Dim secType As String
Dim expiry As String
Dim strike As String
Dim right As String
Dim multiplier As String
Dim exchange As String
Dim curency As String

' get contract description
symbol = UCase(ActiveCell.offset(0, 0).Value)
secType = UCase(ActiveCell.offset(0, 1).Value)
expiry = ActiveCell.offset(0, 2).Value
strike = ActiveCell.offset(0, 3).Value
right = UCase(Left(ActiveCell.offset(0, 4).Value, 1))
multiplier = UCase(ActiveCell.offset(0, 5).Value)
exchange = UCase(ActiveCell.offset(0, 6).Value)
primaryExchange = UCase(ActiveCell.offset(0, 7).Value)
curency = UCase(ActiveCell.offset(0, 8).Value)

' must have symbol, secType, and exchange
If symbol = "" Or secType = "" Or exchange = "" Or curency = "
Then
Beep
MsgBox ("You must enter at least symbol, security type
exchange, and currency.")
Exit Sub
End If

' build server
Dim server As String
server = Range("d5").Value
If server = "" Then
MsgBox ("You must enver a valid user name.")
Exit Sub
End If
server = "=" & Range("d5").Value & "|"

' build topic
Dim topic As String
topic = "tik!"

' build id
Dim id As String
id = "id" & genId & "?"
genId = genId + 1

' build req
Dim req As String
Dim reqType As String

reqType = "req"

req = symbol & "_" & secType & "_"
' build req
If ((secType = "OPT" Or secType = "FUT" Or secType = "FOP") An
expiry = "") Then
reqType = "req2"
Else
If secType = "OPT" Or secType = "FUT" Or secType = "FOP" Then
req = req & expiry & "_"
End If
If secType = "OPT" Or secType = "FOP" Then
req = req & strike & "_" & right & "_"
If multiplier <> "" Then
req = req & multiplier & "_"
End If
End If
End If

req = req & exchange & "_" & curency

If secType = "BAG" Then
req = req & "_" & Cells(ActiveCell.row, 10)
End If

If primaryExchange <> "" Then
req = req & "_" & primaryExchange
End If

' Replace space with string "singleSpace" since dde won't work o
empty space
req = Replace(req, " ", "singleSpace")

' I tried Dims here
Dim myhi As String
Dim mylo As String
Dim mybid As String
Dim myask As String

myask = server & topic & id & "ask"
mybid = server & topic & id & "bid"

If server & topic & id & "bid" < mylo Then
mylo = server & topic & id & "bid"
End If
If server & topic & id & "ask" > myhi Then
myhi = server & topic & id & "ask"
End If


' Place req in spreadsheet
Const reqOffset = 10
ActiveCell.offset(0, reqOffset).Formula = server & topic & id
reqType & "?" & req
ActiveCell.offset(0, reqOffset + 1).Formula = server & topic & id
"bidSize"
ActiveCell.offset(0, reqOffset + 2).Formula = server & topic & id
"bid"
ActiveCell.offset(0, reqOffset + 3).Formula = server & topic & id
"ask"
ActiveCell.offset(0, reqOffset + 4).Formula = server & topic & id
"askSize"
ActiveCell.offset(0, reqOffset + 5).Formula = server & topic & id
"last"
ActiveCell.offset(0, reqOffset + 6).Formula = server & topic & id
"lastSize"
ActiveCell.offset(0, reqOffset + 7).Formula = server & topic & id
"volume"
ActiveCell.offset(0, reqOffset + 8).Formula = server & topic & id
"close"
' I added the next 4 lines'''''''''''''''''''''''''''''''''
ActiveCell.offset(0, reqOffset + 11).Value = myhi
ActiveCell.offset(0, reqOffset + 12).Value = mylo

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
ActiveCell.offset(1, 0).Activate
End Sub

In the spreadsheet bid has this statement in it: =edemo|tik!id15?bid
In the spreadsheet ask has this statement in it: =edemo|tik!id15?ask

In the spreadsheet my ask has this statement in it:
=edemo|tik!id15?ask
In the spreadsheet my bid for some reason has no statement in it

That is the problem it just transfers the the statements to the cel
without any operations on them...

If you need the streaming data I would need to email the whole thing
and you woudl need java installed

Hope this puts a better lite on it?

regards,
rnr

 

[RWN]

It's been a long day so I'm having a little difficulty understanding what you're trying to
accomplish.
But, all your values appear to be string values so you're comparing strings - is this what
you intended?.

For ex. (using the "lo" compare).

Assuming D5 has a value of "Svr" (for discussion sake), then
Server would be "=Svr.Value|"

Topic would be (as per your example) "tik!"

Assuming genld = 1
id would be "id1?"

Then server & topic & id & "bid" equates to "=Svr.Value|tik!id1?bid"
mylo would be null ("")

So your compare is

If "=Svr.Value|tik!id1?bid" < "" Then
mylo = server & topic & id & "bid"
End If

It will never be less than mylo so mylo stays at null.
Conversely the "Hi" compare will have a value (=Svr.Value|tik!id1?ask") because your
string is greater than null.

I'm confused as to how this is supposed to work (I profess ignorance on the "streaming"
concept).
But if something is calling this routine all your variables will be reset each time it is
called,
"genld", for example, will never (I know-never say never!) go beyond 1.


I apologize for my inability to understand and, as much as I'd like to get into this, I
cannot download anything from an unknown source (as many here won't).
Perhaps someone will come along with more knowledge than me (shouldn't be hard to find).

Good Luck and post back when you get it solved-I'll be watching.

 

[rnrss]

Hi RWN thanks for your reply....

In a word it creates a dde string and puts it in a cell in excel the
it will receive data via a dde data server...

So I enter in the stock symbol, security type (like stock or futures)
and the exchange it is traded on and the currency I wish to trade i
in... after doing that it takes that info and creates a string tha
communicates with the dde server and retrieves any data as i
changes...

The ID does increment via the genid + 1 statement and each entry has
value greater than the previous one...

After that it just keeps streaming in data... I need to do couple o
functions, mainly a latch and hold function... If I could get pas
that hurdle then I stand a chance going to the next steps with mat
functions and various tests etc...

regards,
rn

 

[rnrss]

Hello again...

i still have been unable to solve my latching streaming data problem...


Here is what this is designed around for those who need the complete
story behind what I am trying to do... managing the data and actually
doing something with it in vb beyond watching it update has become a
real show stopper here...

http://www.interactivebrokers.com/php/webhelp/Interoperability/DDE_Excel/syntax.htm

regards
rnr