displaying eSignal quote in excel

[hollins]

I have an excel worksheet which has a list of option symbols, example... adi jh, and I obtain a matching list of bid quotes from eSignal by use of the cell entry... =WINROS|Bid!'adi jh'. This works fine, but whenever I change the option symbol I have to go into each bid quote cell and manually change that to get the update. What I want to do is write a function for the bid quote cell, passing through the option symbol cell, so that when I change the contents of the option symbol cell the bid quote cell function will automatically update the quote.

I tried the following function...

Function OtherOptionBid(symbol)
OtherOptionBid = "=winros|bid!'" & symbol & " ' "
End Function

but instead of returning the bid quote, for the example of adi jh as the symbol, it returns the text...

=WINROS|Bid!'adi jh'

If I manually enter this same text in the cell it returns the bid quote, but my function will not do it automatically.

Can anyone show me how it can be done?

[eSignal_JasonK]

Hello Hollins,

In the spreadsheet below you will find two different methods for updating the DDE link by changing a symbol in a separate cell. Method 1 uses a cell to reference the symbol and Method 2 uses a text box. Using the text box is the only way to have the bid automatically update when you change the symbol in the text box. If you don't want to use text boxes then use method 1. In Method 1, you would enter the symbol in cell A4, press enter and then click the "Update Symbol" command button. Hope this helps.

UpdateBid.xls

[hollins]

JasonK,

Thanks for your suggestions.

I gather then that I cannot use a function() to have an automatically updated quote. I have to use a sub() and physically do something to retrieve the result, such as click on the button in your example.

I have incorporated the button call in my spreadsheet, which significantly reduces the manual typing I was doing before.

Short of a completely automatic solution, this was a great help!

Re the textbox on your UpdateBid spreadsheet, how do I select it to see how you linked it to the Symbol_Change macro? You have done something to the box to make it unselectable (sorry to bother you with basic Excel questions...). If I can figure this out then the textbox method may be even better for me.

[mgraczyk]

Hollins - JasonK attached Visual Basic for Applications (a programming language) code to the text box to dynamically manipulate the equation in cell A16.

If you examine the Visual Basic code associated with the Sheet1 worksheet of his Excel file, you will see that the code associated with the text box is:

Private Sub Symbol_Change()
Dim sBid As String
Dim sSymbol As String

sBid = ("=WINROS|Bid!")
sSymbol = "'" + Symbol.Value + "'"
sBid = sBid + sSymbol

Range("A16").Formula = sBid
End Sub

[eSignal_JasonK]

Hello hollins and mgraczyk,

mgraczyk, thanks for stepping in to help out here. hollins, please accept my apologies for not responding to your post sooner.

You could use a function, but it would have to be called from subroutine that is triggered by some event like the change or click event. You still have to use a similar routine like in the current spreadsheet.

To select the text box to view it's properties and associated code you have to be in "design" mode. If you have the VBA stuff installed with Excel you should be able to go to View-->Toolbars-->Visual Basic to access the Visual Basic toolbar. Then click on the Design Mode icon.



Now you can select the text box object, right-click and select Properties or View Code. If you select View Code the VBA editor will open up and show you the two sub routines, Symbol_Change() and UpdateSymbol_Click(). The first part of the name is the object. "Symbol" was the name I assigned to the text box in the properties window. The second part of the name is the event you select from the drop-down menus at the top of the VBA editor. You can create new subs by selecting the object name from the object drop-down menu and an event from the procedures drop-down menu. Then add some code of course. Hope this helps.