Seeking Help for a Stock-Data-Server to use with Excel

[andre drabs]

Hi all


First of all, i got the PbWin9.01 today.
The bad part of the story is that, untill now, i only "used" PbDos3.50 so i dont have any experiance in programming with PbWin.

The idea is the following:
1) A PbWin9.X program should run in the systray, in the background, grabbing stock-data from the Yahoo-website based on tickers located in an Excel spreadsheet. Download-time should also be based on user-settings in Excel (somewhere a program-setting / can be located in the registry). This PbWin-program receives his data in csv-form what then need to be passed-on to excel. Or the program saves the csv-file, or the data is passed to excel in any other way. Speed is what i am looking for, meaning that if Excel is more quick to open a csv then that is fine else if excel is slower then i would prefer another solution (what ever that might be)

The meaning is that gathering and transferring the captured data may-not "disturb" excel, meaning that it cannot be that excel freeze for secs/min while importing/receiving new data.

2) the excel file has screens where data is updated following user's instuctions (1/2/5/10/30 secs or any other value he find OK) and following the PbWin program's data.

The Excel-file is ready but any adaption is no problem.

I normaly should have made everything in ExcelVba but excel is to slow and freeze toooooo long while importing the data.

Anybody has ID's ?

Note that this is a private-use-program (not commercial) so for those who find solutions where i have to buy (their) additional sofware: forget-it.

Thanks for reading.

[Scott Slater]

Congratulations!! (on your new purchase)

PBWin is a different animal the PBDOS, so don't expect to learn everything overnight. Much of it is the same, so you have a head start, but you're still writing Windows code which works a bit differently.

I would start by just playing around with some DDT forms and seeing how the code executes. Look at the sample programs, specifically the Tray.BAS file found under the Sdk sub-folder in the Samples folder.

Things will eventually begin to make sense, but the sample data and the forums here are a great place to start.

[Michael Mattias]

How is this program supposed to know "it's time to go get something from the url?" Is this the "ticker from Excel" thing? Is this supposed to be an Excel plug-in? If so, you could write it like that.

FWIW, I think this can be done without a separate program by telling Excel its data is located at some URL, but you'd have to find someone who knows Excel a heck of a lot better than do I. Maybe there is an Excel Newsgroup somewhere (oh heck, there MUST be an Excel newsgroup somewhere). -

[andre drabs]

How is this program supposed to know "it's time to go get something from the url?" Is this the "ticker from Excel" thing? Is this supposed to be an Excel plug-in? If so, you could write it like that.

FWIW, I think this can be done without a separate program by telling Excel its data is located at some URL, but you'd have to find someone who knows Excel a heck of a lot better than do I. Maybe there is an Excel Newsgroup somewhere (oh heck, there MUST be an Excel newsgroup somewhere). -

Michael,

I have the code in VBA to do that same thing (grabbing the info from Yahoo). The problem is that while excel is downloading the csv-file from yahoo and then grab the content from that csv-file to put it in the right cells, excel freeze the user (in this case: me) for seconds/minutes (depending on the number of tickers to download). So doing all this in excel is not the way to go.

Note that yahoo is limitted to +-50 unique stock-tickers at a 1 time-query (and csv-file) and if someone (me again) wants to have quotes for lets say 500 from the availlable tickers, the excel-function need to "grab" 10 times before his work is done. Grabbing 10 times at lets say 2-5 sec per each time means tooooo much time that excel freezes.

The way to go is to have a PB-program downloading the data from yahoo in the background, do al the needed conversions and then pass that ready-formatted-data to excel.

I try to use the excel sample in the com-section from the samples dir (installed by Pb9) but PbWin is completly new to me.

I'm sure that more people are "in stocks" so this program could be usefull to more than just myself.

[andre drabs]

How is this program supposed to know "it's time to go get something from the url?" Is this the "ticker from Excel" thing? Is this supposed to be an Excel plug-in? If so, you could write it like that.

FWIW, I think this can be done without a separate program by telling Excel its data is located at some URL, but you'd have to find someone who knows Excel a heck of a lot better than do I. Maybe there is an Excel Newsgroup somewhere (oh heck, there MUST be an Excel newsgroup somewhere). -

The time-to-check Yahoo can be read from or the excel-file, or the excel-ini-file or even from registry. It even can be read from a whatever ini-file where i can have access to from excel. The way how is not important. Excel dont has much secrets for me.

The stocks to download can also be read-out from the excel-file.

The downloaded data (quotes, dayhigh, daylow etc) from yahoo will need to be placed-back in excel, it's logic that data from f.e. Microsoft-ticker will be placed in the Microsoft-row in excel.

[jcfuller]

Now that you have PBWin is Excel even necessary? Most of your vba code should port to Pb reasonably well.

James

[Michael Mattias]

Interesting problem... which appears to be in that Excel is executing your VBA script synchrounously, not allowing Excel to do anything else until it's done.

So the problem becomes 'executing the script asynchronously.'

I can see a way to do this:

A server program (in your example, your 'tray' application, but you don't need that program to be always running, read on) could download the info, and then tap into the executing instance of Excel using the COM interface and update the sheet(s) of interest.

Your server program could call the existing script, so you would not have to rewrite all that code.

The only problem I see here is telling the server program it's time to go get some data.

But perhaps that could be done with ANOTHER VB script called from Excel....

What that script would have to do would be to START your server/utility program, passing it the name of the stock and the row and column to be updated with the data. and return... Excel is now 'back in business' as far as the user is concerned.. almost instantaneously.

The asynchronously-executing utility program (it's not really a server anymore so I won't call it that) does its thing (download info of interest) and then updates the running instance of Excel.

What I see would see as the major obstacle here is finding a way to update the sheet when the utlity program has the formatted data ready for use... every time I've tried to do anything with a running instance of Excel, Excel does not seen to allow that.

When the utility program is done updating the sheet, it's done and may end.

If the COM interface won't allow update, maybe you could try the DDE interface. (I know, I know: DDE - NOBODY uses that anymore!)

It's a thought.

MCM

[andre drabs]

Now that you have PBWin is Excel even necessary? Most of your vba code should port to Pb reasonably well.

James

Did you read that i am used to PbDos untill 2 days ago?

Don't think that in 2 days someone can program something decent in PbWin!

Calling porting VBA-Excel to PbWin an easy job???
I don't think that you know the differance between both otherwise you should not say something like that.

[andre drabs]

Interesting problem... which appears to be in that Excel is executing your VBA script synchrounously, not allowing Excel to do anything else until it's done.

So the problem becomes 'executing the script asynchronously.'

I can see a way to do this:

A server program (in your example, your 'tray' application, but you don't need that program to be always running, read on) could download the info, and then tap into the executing instance of Excel using the COM interface and update the sheet(s) of interest.

Your server program could call the existing script, so you would not have to rewrite all that code.

The only problem I see here is telling the server program it's time to go get some data.

But perhaps that could be done with ANOTHER VB script called from Excel....

What that script would have to do would be to START your server/utility program, passing it the name of the stock and the row and column to be updated with the data. and return... Excel is now 'back in business' as far as the user is concerned.. almost instantaneously.

The asynchronously-executing utility program (it's not really a server anymore so I won't call it that) does its thing (download info of interest) and then updates the running instance of Excel.

What I see would see as the major obstacle here is finding a way to update the sheet when the utlity program has the formatted data ready for use... every time I've tried to do anything with a running instance of Excel, Excel does not seen to allow that.

When the utility program is done updating the sheet, it's done and may end.

If the COM interface won't allow update, maybe you could try the DDE interface. (I know, I know: DDE - NOBODY uses that anymore!)

It's a thought.

MCM

You describe the workflow exact, except for 2 things.
1) After excel "import" or "receives" the data it only has 1 second (or less) work to recalc the whole thing. After that, excel is idle untill the next data-load arrives.

2) The utility program has to continue running since stocks need to be updated regulary (meaning with intervals of lets say 15sec). The "order" to start downloading can be given from excel from within an "on timer"-routine or even a default on-timer-value.

For now, i'm playing with the excel-com demo to jump-start learning how it can or have to work. I first need to "get-the-logic" behind PbWin before being able to program something decent.

[Michael Mattias]

) After excel "import" or "receives" the data it only has 1 second (or less) work to recalc the whole thing. After that, excel is idle untill the next data-load arrives.

And? You can't do anything about the speed at which Excel recalculates. As long as you are connected to that instance, issue a "recalculate" command. There should be a METHOD to do that.

2) The utility program has to continue running since stocks need to be updated regulary (meaning with intervals of lets say 15sec). The "order" to start downloading can be given from excel from within an "on timer"-routine or even a default on-timer-value

Why does it have to continue running? Why can't it start, get data, update the spreadsheet and end? And do that every 15 seconds, or on demand? Don't get locked in to something before you consider alternatives!

You certainly COULD have a program always running and doing a "download, update" cycle every 15 seconds. But that would be silly... because that means you would have to get ALL the data on every download. With the "on demand" concept you could have your utility get ONLY the data you want/need at that time.

If you only want changes, then downloading "current stock price" is also, shall we say, "non-Optimal." I know there are 'feeds' of stock tickers available... you could monitor that for transactions for 'symbols of interest' and only update your spreadsheet when one of those symbols is affected.

MCM

[Houng Chang]

If you are not using PBwin to do plotting/analysis, it is easy.

1. In Yahoo, build up your protfolio with up to 200 ticker symbols. Build as many portfolios as needed.

2. In Excel, create as many worksheets as protfolios. On each worksheet, insert the hyperlink to each Yahoo portfolio.

3. In ExcelVBA, use Selection.QueryTable.Refresh BackgroundQuery:=False to get data on each worksheet. Speed is quite high.

4. Then you can do anything with the data using ExcelVBA.

5. Or you can output data to a file, and have a PBwin program running simultaneously to analyze the data.

Hope this helps.

[andre drabs]

If you are not using PBwin to do plotting/analysis, it is easy.

1. In Yahoo, build up your protfolio with up to 200 ticker symbols. Build as many portfolios as needed.

2. In Excel, create as many worksheets as protfolios. On each worksheet, insert the hyperlink to each Yahoo portfolio.

3. In ExcelVBA, use Selection.QueryTable.Refresh BackgroundQuery:=False to get data on each worksheet. Speed is quite high.

4. Then you can do anything with the data using ExcelVBA.

5. Or you can output data to a file, and have a PBwin program running simultaneously to analyze the data.

Hope this helps.

I know all this. I use it in this way now. The problems are 1) The web-querries takes too long. 2) while updating the web-querries the user is "froozen". 3) I hate being blocked by excel making me wait for his results.

What I want to do is "free" excel from the querry-work so that I'm not "blocked" during that time. The querry can be made by PB in another "proces" and only the final data is transferred to excel. That way, it will save a lot of time for the same result.

I dont say your suggestion is bad, it's only not good enough for me.

[Edwin Knoppert]

Using Excel via a com interface will be slower if Excel already runs.

[andre drabs]

Using Excel via a com interface will be slower if Excel already runs.

Depends on what you want to do.

I bet that the combination PB to download and prepare data to move-in excel + Excel to receive the data and do its own calcs will be way faster than using only excel to do it all.

The simple reason why is that the PB program runs in the background and is not disturbing excel's working while it takes out of excel's hands the most time-consuming tasks.

I am even thinking on a way to make the Pb program move the collected data into memory from where excel could simply grab it. (if that should be possible...)

[Edwin Knoppert]

Using Excel via a com interface will be slower if Excel already runs.

I was not discussing a different technique to process the data at all.

[Michael Mattias]

>> FWIW, I think this can be done without a separate program ....
> but you'd have to find someone who knows Excel a
>heck of a lot better than do I

>If you are not using PBwin to do plotting/analysis, it is easy....

Hmm, seems Mr. Chang qualifies.....


MCM

[Houng Chang]

Hi Michael, Andre, and Edwin:

In my ordinary computer system, I update 3 portfolios (in 3 worksheets) with total 500+ ticker symbols. The time to refresh a cycle is about 12 seconds. The data can be analyzed in Excel in ExcelVBA program and output (Buy/Sell/Hold) results to a summary worksheet.

In Excel, to create Web data gathering is done by this:

Data > FromWeb > (enter web link) > Import > (enter A1) > OK

Then the Query stays with the worksheet. Refresh the workbook by activate the worksheet. Use macro recording then the programming is in the macro subroutine.


The whole process is fast.

The problem of Excel is graphic is poor. It takes PBwin to plot meaningful chart.

Edwin taught me 6 months ago to handle Excel array in PBwin DLL. Great help from Edwin. Thanks.

Hope this helps.

HYC

[andre drabs]

The time to refresh a cycle is about 12 seconds.

For me that is way to long, there is at least 11 secs to cut from that time if a PbWin program downloads and prepares the data. More, i dont want to create ports in Yahoo, I want to be able to change tickers from within my excelsheet whereafter "that data" is downloaded from Yahoo. In your system, you first need to modify your Yahoo-port before your excel-sheet can show you the change.

In Excel, to create Web data gathering is done by this ...
Then the Query stays with the worksheet. Refresh the workbook by activate the worksheet. Use macro recording then the programming is in the macro subroutine.

I know that possibility in excel but dont use it. I can write my own subs and call that code when i need/want the data to be refreshed. That is more simple i think. The only problem with excel-web-querries is the time it needs to do his job (in your case 12 whole secs) while excel completely freeze you in that time.

It takes PBwin to plot meaningful chart.

??? There is a whole collection of decent charts in excel. Export data to PB and create charts in PB will again make you wait for a while.

Edwin taught me 6 months ago to handle Excel array in PBwin DLL. Great help from Edwin. Thanks.

Do you (or Edwin) want to share that script? I think that it's indeed usefull in the application i want to write, especially in passing data from and to excel from PB.

[andre drabs]

I found in the forum this:

http://www.powerbasic.com/support/pbforums/showthread.php?t=23887&highlight=Excel+array+PBwin+DLL

I did some mods to it in the excel-part.
My result-times (on a NetBook AcerOne-150 with ATOM Cpu running at 1.6 gh) are cut in 3 !!

I think that this code is usefull to inject my Yahoo-data in excel, just need a re-work of the code.

Note that the code is "playing" with 3*30.000 cells, meaning that the Yahoo-Data for let's say 2000 tickers can have 45 different types of data (day-high, day-low, bid, ask, etc) before i reach that number of cells to manipulate. This routine takes +- 2.1 secs to put everything in place (far less than the 12 secs to download +- 500 tickers with not as much as 45 types of data).

This means that the ID to download with Pb and then transfer the data to excel will work much faster than doing everything in Excel.

Option Explicit
Declare Function ModvArray Lib "ModvArry.dll" (vArrayNoParen As Variant, vDllStartTime As Variant) As String

Sub StartTest()
  Dim Stime As Variant, TestString As String

  'output timings to cell J1 in sheet1
  Stime = Timer
  Range("J1").Value = Test(TestString) + vbNewLine + "after function return " & Timer - Stime
  
  'output timings to MsgBox
  Stime = Timer
  MsgBox Test(TestString) + vbNewLine + "after function return " & Timer - Stime
End Sub

Function Test(TestString)
  Dim Stime As Variant
  Stime = Timer

  Dim vArrayNoParen As Variant, lRows As Variant, lCols As Variant
  Dim oRange As Range, ubrows As Variant, ubcols As Variant
  Dim SheetName As Variant, vDllStartTime As Variant
  Dim vPutInCellsStartTime As Variant, SReturnValue As String

  'much larger than 100k cells and it slows down tremendously
  lRows = 30000
  lCols = 3
  SheetName = "Sheet1"
  ChDrive ThisWorkbook.Path
  ChDir ThisWorkbook.Path
  ThisWorkbook.Worksheets(SheetName).Activate
  Range(Cells(1, 1), Cells.SpecialCells(xlLastCell)).Delete
  Range(Cells(1, 1), Cells(lRows, 1)).Formula = "=TEXT(ROW(),""000"")"
  Range(Cells(1, 2), Cells(lRows, 2)).Formula = "=row()"
  Range(Cells(1, 3), Cells(lRows, 3)).Value = "something"
  Cells(4, 1).Value = Empty
  TestString = "Start to before assigning cells " & Timer - Stime
  'by far the fastest way to get cell data is a no paren variant
  vArrayNoParen = Range(Cells(1, 1), Cells(lRows, lCols)).Value
  TestString = TestString + vbNewLine + "after assign cells " & Timer - Stime
  vDllStartTime = Timer
  SReturnValue = ModvArray(vArrayNoParen, vDllStartTime)
  TestString = TestString + vbNewLine + SReturnValue
  TestString = TestString + vbNewLine + " time within dll " & Timer - vDllStartTime
  TestString = TestString + vbNewLine + "start to after dll " & Timer - Stime
  If SReturnValue = "Error" Then
     MsgBox "dll returned error"
  Else
     vPutInCellsStartTime = Timer
     ubrows = UBound(vArrayNoParen, 1)
     ubcols = UBound(vArrayNoParen, 2)
     Set oRange = Cells(1, 5)
     'oRange.Resize(ubRows, ubCols).NumberFormat = "@" 'to force text format
     oRange.Resize(ubrows, ubcols).Value = vArrayNoParen
     TestString = TestString + vbNewLine + " time to put in cells " & Timer - vPutInCellsStartTime
  End If
  Set oRange = Nothing
  TestString = TestString + vbNewLine + "done within function " & Timer - Stime
 Test = TestString
End Function

[B][U]Original timings by John Hackl:[/U][/B]
'30000 rows, 3 columns
'Start to before assigning cells 0.8808594
'after assign cells 1.041016
' dll crankup time -000.00
' dll up to mod array 000.05
' dll past mod array 000.22
' dll past assign 000.71
' time within dll 1.148438
'start to after dll 2.189453
' time to put in cells 2.910156
'done within function 5.099609
'after function return 6.089844

[B][U]Timings by me:[/U][/B]
'Start to before assigning cells 0,625
'after assign cells 0,78125
'dll crankup time -000.00
'dll up to mod array 000.16
'dll past mod array 000.34
'dll past assign 000.44
'time within dll 0,484375
'start to after dll 1,265625
'time to put in cells 0,78125
'done within function 2,046875
'after function return 2,09375

Below is the code for the DLL
It compiles & works-fine in PbWin9.01
I did not made any changes there (i first need to understand the logic of PbWin) but since the code was posted in 2003 (by John Hackl) and for PbWin7.03 i assume that adjustments can be or should be made to shorten code-executing-time . (just a guess)

'
Demonstration of modifying excel data with a pb dll using a
'variant no paren array
'Useful up to 100k cells, larger than that is very slow
'Requires pbwin7.02
#COMPILE DLL "ModvArry.dll"
#DIM ALL
FUNCTION ModvArray ALIAS "ModvArray" (vArrayNoParen AS VARIANT,vDllStartTime AS VARIANT) EXPORT AS STRING

   DIM dDllStartTime AS DOUBLE
   LET dDllStartTime = VARIANT#(vDllStartTime)

   DIM sReturnInfo AS STRING
   sReturnInfo = " dll crankup time " &  FORMAT$(TIMER - dDllStartTime,"000.00") & $CRLF

   IF VARIANTVT(vArrayNoParen) = 0 OR VARIANTVT(vArrayNoParen) = 1 THEN
      MSGBOX "nothing in vInArray"
      FUNCTION = "Error"
      EXIT FUNCTION
   END IF

   DIM i AS LONG
   DIM j AS LONG
   DIM vArray2Dim(0) AS VARIANT
   LET vArray2Dim() = vArrayNoParen
   DIM ubrows AS LONG
   DIM ubcols AS LONG

   ubrows = UBOUND(vArray2Dim(),1)
   ubcols = UBOUND(vArray2Dim(),2)
   IF ubrows = 0 OR ubcols = 0 OR ubrows = -1 OR ubcols = -1 THEN
       MSGBOX "InArray has no dimensions"
       FUNCTION = "Error"
       EXIT FUNCTION
   END IF
   sReturnInfo =  sReturnInfo & " dll up to mod array " &  FORMAT$(TIMER - dDllStartTime,"000.00") & $CRLF

   DIM vtype AS LONG
   FOR j = 1 TO ubcols
      FOR i = 1 TO ubrows
         vtype = VARIANTVT(vArray2Dim(i,j))
         IF vtype = 0  OR  vtype = 1 THEN ' nothing or null
            vArray2Dim(i,j) = "was a blank" & STR$(i) & STR$(j)
         ELSEIF vtype = 8 THEN 'string
            vArray2Dim(i,j) = "string " & VARIANT$(vArray2Dim(i, j))
         ELSEIF vtype => 2 AND vtype <= 6 THEN
            vArray2Dim(i,j) = VARIANT#(vArray2Dim(i, j)) + 100
         END IF
       NEXT i
   NEXT j
   sReturnInfo =  sReturnInfo & " dll past mod array " &  FORMAT$(TIMER - dDllStartTime,"000.00") & $CRLF

   LET vArrayNoParen = vArray2Dim()
   sReturnInfo =  sReturnInfo & " dll past assign " &  FORMAT$(TIMER - dDllStartTime,"000.00")

   FUNCTION = sReturnInfo
END FUNCTION

What is left to do now is:
Program a PbWin program to make it
1) download the data in the background
2) read and convert the downloaded CSV-file to an array
3) pass that array to the dll (or make the data accessible for the dll) for further processing

Program in Excel to make excel refresh the data when new data is availlable

PS. I removed the RamDisk part of this post because running it on a RamDisk slows it down by half (3 sec instead of 2.1 sec). Still wondering why, maybe excel use a temp-file who is not on the Ramdisk but that dont explain why it is that much slower running from the ramdisk !!!

[Houng Chang]

More, i dont want to create ports in Yahoo, I want to be able to change tickers from within my excelsheet whereafter "that data" is downloaded from Yahoo. In your system, you first need to modify your Yahoo-port before your excel-sheet can show you the change.

Andre,

Your idea here is quite advanced and beyond what I know. But I believe in order to grab any data from YAHOO data source, one needs to know YAHOO's internal data structure. What I am doing is just utilizing YAHOO's output web page. Therefore if I need a new ticker data I need to update YAHOO portfolio.

For the passing array between ExcelVBA and PBwin, you may search my post (quite few) within the last year.

HYC