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..

--
rnrs
-----------------------------------------------------------------------
rnrss's Profile: http://www.excelforum.com/member.php...fo&userid=2782
View this thread: http://www.excelforum.com/showthread.php?threadid=47568

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

If you need "lo"/"hi" then put them inside their respective IF statements.
--
Regards;
Rob
------------------------------------------------------------------------
"rnrss" wrote in message
...

Hello everyone...

I am trying to capture the highest high and lowest low in streaming
data. What I have done is not working correctly and I am at a loss how
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 lowest
low of the stream and increment each time a new hi was or lo was
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 as
I expected it should, it enters the ask string rather than a value... I
dont think it is possible to latch anything in the spreadsheet and to
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 and
paint an output to a cell that can be further operated on once in
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 with
the ask where cell 22 <=q8

then I told vb to get the values and rewrite them into cells 23 and 24
respectively just to see if it would work and I get a type mismatch
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...


--
rnrss
------------------------------------------------------------------------
rnrss's Profile: http://www.excelforum.com/member.php...o&userid=27823
View this thread: http://www.excelforum.com/showthread...hreadid=475688

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

--
rnrs
-----------------------------------------------------------------------
rnrss's Profile: http://www.excelforum.com/member.php...fo&userid=2782
View this thread: http://www.excelforum.com/showthread.php?threadid=47568

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).

--
Regards;
Rob
------------------------------------------------------------------------
"rnrss" wrote in message
...

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


--
rnrss
------------------------------------------------------------------------
rnrss's Profile: http://www.excelforum.com/member.php...o&userid=27823
View this thread: http://www.excelforum.com/showthread...hreadid=475688

Hello thanks again for your response...

Here is a pic of what it looks like:
http://i22.photobucket.com/albums/b3...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


--
rnrss
------------------------------------------------------------------------
rnrss's Profile: http://www.excelforum.com/member.php...o&userid=27823
View this thread: http://www.excelforum.com/showthread...hreadid=475688

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.

--
Regards;
Rob
------------------------------------------------------------------------
"rnrss" wrote in message
...

Hello thanks again for your response...

Here is a pic of what it looks like:
http://i22.photobucket.com/albums/b3...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") And
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 on
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 cell
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


--
rnrss
------------------------------------------------------------------------
rnrss's Profile: http://www.excelforum.com/member.php...o&userid=27823
View this thread: http://www.excelforum.com/showthread...hreadid=475688