Excel OnQuoteChanged list of stocks

[superpolo]

New to ActiveX integrated to Excel I need some help on a simple task.

I need to modify the code to the OnQuoteChanged to get streams on real time quotes on about 100 stocks

from the original GetBasicQuote code :
Private Sub esignal_OnQuoteChanged(ByVal sSymbol As String)
Dim quote As IESignal.BasicQuote
quote = esignal.GetBasicQuote(sSymbol)
Sheet1.Cells(5, 3) = quote.dBid
Sheet1.Cells(5, 4) = quote.dAsk
Sheet1.Cells(5, 5) = quote.dLast
End Sub

question: must i create a loop inside that private sub that will reprint each cell on each quote change, but then, which quote change would be used by the system? the last symbol entered only???

question 2: there must be a simpler way to do that ? anyone knows ?

question 3: this is the code for a "Basic" quote. Is there the same thing for an "Extended" quote that would provide more daily information ?

Thanks

(Thanks GenSpoo "George" for your previous answer)
_________
Nothing Super about my poor programming skills, only my logon which makes me feel better...

[GenSpoo]

Again, my usual disclaimer when talking about Excel... "I am not an Excel person, so take what I say with a grain of salt"

Looking at the code snippet you provided compared to what it sounds like you want, the problem is that the sample uses hardcoded cell numbers for the results. If you want to see quotes for 100 symbols, then what I would do would be to create some sort of lookup table (i.e. hash table, dictionary, whatever you want to call them), and use the symbol name as the key and the location (cell number(s)) as the values. You could also create a large array with an appropriate structure to store that info, but going through the loop on every event might not be a good use of CPU cycles, especially as the number of symbols grow. You could also have the function just walk the rows looking for the correct row by comparing the symbol name in row X and column Y. When they match, you then know the correct cell row (basically the same concept as the array mention, just using the sheet as the array).

Anyway, you could then do something like this (hash version):

Private Sub esignal_OnQuoteChanged(ByVal sSymbol As String)
Dim quote As IESignal.BasicQuote
quote = esignal.GetBasicQuote(sSymbol)
rowNumber = hashLookup(sSymbol)
'Sheet1.Cells(rowNumber, 2) = sSymbol
Sheet1.Cells(rowNumber, 3) = quote.dBid
Sheet1.Cells(rowNumber, 4) = quote.dAsk
Sheet1.Cells(rowNumber, 5) = quote.dLast
End Sub

or something like this (sheet version):

Private Sub esignal_OnQuoteChanged(ByVal sSymbol As String)
Dim quote As IESignal.BasicQuote
quote = esignal.GetBasicQuote(sSymbol)
for rowNumber = 1 to maxRows
if Sheet1.Cells(rowNumber, 2) = sSymbol then
Sheet1.Cells(rowNumber, 3) = quote.dBid
Sheet1.Cells(rowNumber, 4) = quote.dAsk
Sheet1.Cells(rowNumber, 5) = quote.dLast
'done so break loop to save cycles
break
end if
next
End Sub

Sorry for the potentially incorrect VB syntax. I blame my C++ background.

There currently is no "ExtendedQuote" function. To get more information than is supplied by the BasicQuote, you would need to retrieve bar data and do whatever calculations were necesarry.

Cheers... George