repost: Need Help With Steaming Data Macro  
Author Message
thirdhandd





PostPosted: Wed Oct 12 22:49:39 CDT 2005 Top

Excel Programming >> repost: Need Help With Steaming Data Macro

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.hide-link.com/ ;userid=2782
View this thread: http://www.hide-link.com/

Excel246  
 
 
RWN





PostPosted: Wed Oct 12 22:49:39 CDT 2005 Top

Excel Programming >> repost: Need Help With Steaming Data Macro 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.
--
Regards;
Rob
------------------------------------------------------------------------


>
> 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?action=getinfo&userid=27823
> View this thread: http://www.excelforum.com/showthread.php?threadid=475688
>


 
 
rnrss





PostPosted: Thu Oct 13 13:46:52 CDT 2005 Top

Excel Programming >> repost: Need Help With Steaming Data Macro
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?action=getinfo&userid=2782
View this thread: http://www.excelforum.com/showthread.php?threadid=47568

 
 
RWN





PostPosted: Thu Oct 13 21:40:40 CDT 2005 Top

Excel Programming >> repost: Need Help With Steaming Data Macro 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
------------------------------------------------------------------------


>
> 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?action=getinfo&userid=27823
> View this thread: http://www.excelforum.com/showthread.php?threadid=475688
>


 
 
rnrss





PostPosted: Fri Oct 14 11:51:40 CDT 2005 Top

Excel Programming >> repost: Need Help With Steaming Data Macro
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


--
rnrss
------------------------------------------------------------------------
rnrss's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27823
View this thread: http://www.excelforum.com/showthread.php?threadid=475688

 
 
RWN





PostPosted: Fri Oct 14 23:01:27 CDT 2005 Top

Excel Programming >> repost: Need Help With Steaming Data Macro 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
------------------------------------------------------------------------


>
> 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") 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?action=getinfo&userid=27823
> View this thread: http://www.excelforum.com/showthread.php?threadid=475688
>


 
 
rnrss





PostPosted: Sun Oct 16 04:19:32 CDT 2005 Top

Excel Programming >> repost: Need Help With Steaming Data Macro
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

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

 
 
rnrss





PostPosted: Mon Oct 17 12:47:11 CDT 2005 Top

Excel Programming >> repost: Need Help With Steaming Data Macro
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


--
rnrss
------------------------------------------------------------------------
rnrss's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27823
View this thread: http://www.excelforum.com/showthread.php?threadid=475688