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

[andre drabs]

I had some spare time and wrote a routine in VBA to read a .csv-file.

The code for the function (to place in a module in excel):

Private Function ReadTickerCSV(MyFileName As String) As String
 ' Dim all vars
 Dim MyFileNumber As Integer, I As Integer
 Dim MyFileIsOpen As Boolean
 Dim ErrMsg As String
 
 ' Allocate a free filenumber
 MyFileNumber = FreeFile
 
 ' Trap errors if any occur
 On Error GoTo ErrHandler
 
 ' Clear Previous Errors if any
 Err.Clear
 
 ' Create fake error (for debugging) - Remmed-out in normal version
 ' Err.Raise 6
 
 ' Open the file
 Open MyFileName For Input Access Read Shared As #MyFileNumber
 
 ' If we got here the file has opened successfully
 MyFileIsOpen = True

 ' Read the entire contents in one single step
 ReadTickerCSV = Trim(Str(LOF(MyFileNumber))) & Chr(0) & _
  Input(LOF(MyFileNumber), MyFileNumber)

ErrHandler:
 ' Close the file if still open
 If MyFileIsOpen Then Close #MyFileNumber
 
 ' If/when error trapped
 If Err Then
  ReadTickerCSV = "Error #" & Str(Err.Number)
 End If
 
 ' Reset error trapping
 On Error GoTo 0
End Function

The code for testing:

Sub TestReading()
 ' Initiate timer
 sTimer = Timer
 
 ' Dim all vars
 Dim MyPath As String, MyFileName As String, DataFromFile As String
 Dim BytesRead As Long, CellsRead As Long
  
 ' Set FileName to your file
 MyPath = ActiveWorkbook.Path
 MyFileName = MyPath & "\TickData.csv"
 
 ' Read the data
 DataFromFile = ReadTickerCSV(MyFileName)
 
 ' If/when no error occured
 If Not Mid(DataFromFile, 1, 7) = "Error #" Then
  BytesRead = Val(Mid(DataFromFile, 1, InStr(1, DataFromFile, Chr(0))))
  DataFromFile = Mid(DataFromFile, InStr(1, DataFromFile, Chr(0)) + 1)
  BytesRead2 = Len(DataFromFile)
  CellsRead = 501 * 50
  
  ' Display info from 2 rows for test - modify hardware to your own values
  MsgTxt = "Test Hard- & Software:" & vbNewLine & _
           "Computer: Acer NetbookOne" & vbNewLine & _
           "Processor: Intel Atom N270 Dual Core @ 1.60 GHz" & vbNewLine & _
           "Memory: 1024 MB @ 800 MHz + Windows managed page-file" & vbNewLine & _
           "Operating System: Windows XP SP3 installed" & vbNewLine & _
           "Office version: Excel2003 - VBA version: 6.5.1024" & vbNewLine & _
           vbNewLine & "Test Results:" & vbNewLine & _
           "Bytes read from file = " & Format(BytesRead, "###,###,###") & vbNewLine & _
           "Cells read from file = " & Format(CellsRead, "###,###,###") & vbNewLine & _
           vbNewLine & _
           "File Contents (Limited to 2 records): " & vbNewLine & _
           Mid(DataFromFile, 1, 538) & vbNewLine & _
           "Total time wasted in function " & Format(Timer - sTimer, "#0.####0") & " seconds."
  
  MsgTitle = " Test-result For Reading a .CSV-file From Within Ms-Excel2003"
  MsgBox MsgTxt, vbInformation, MsgTitle
   
 Else
  
  ' Display the error if/when one occured
  MsgBox DataFromFile & " caused this function to end unexpectedly." & _
         vbNewLine & "Please contact the author for debugging."
 End If
End Sub

The test-results-picture in atachment.
Speed is not an issue, the function reads 170.050 bytes (corresponding with the values of 25.050 cells in the .Csv-file) in 0.03125 seconds !!

Since i will only need to read about 2.500 cells from the .csv-file, the transfer can be done without the user noticed "something" happend.

Left to do is:
1) The excel-part
a) Write a function to "cut" the data-string in 2.500 "cells" so that they can be copied to their corresponding cells in the worksheet.
b) Write a function to write a .csv-file from the ticker-list in excel (max 50 tickers or 50 cells)

2) The Pb-Part
a) Read needed tickers from the .csv-file
b) Download the .csv-file from Yahoo
c) Store the downloaded file in the current dir

With a little luck, this weekend i can post the complete code here ...

[Michael Mattias]

> Also try to find-out Excel can share memory with Pb...

See: "Dynamic Link Libraries" in your PB Help and CALL function in your Excel reference.

MCM

[andre drabs]

>So my plan is....

That looks like it has all the steps you need. But it kind of reminds me of batch files or mainframe JCL... there are just so many places something can go wrong that troubleshooting (as in debugging) could be a nightmare.

As far as I can tell there are two schools of thought for this kind of process:
1. Many small, discrete steps in which exactly one thing happens
2. One "big" program which "does it all."

For some reason I'm a "#2" guy. At least now I am. In Ye Olden Dayes (MS-DOS, OS/VS) the limits on memory and the slowness of computers more or less dictated the "#1" approach.

In this case, i think the "#1" solution will give "more speedy" results. Doing everything in excel will make me wait the whole time Excel need to download the data. When the data is downloaded, Excel stil needs to read the data from the csv. When Pb does the download-part in a background-task i will only have to "wait" for the transfer of the data from PbWin (or memory) to Excel. The data itself will be limitted to max 80 tickers returning max 40 different informations (meaning 3200 cells).

Now testing how long Excel is busy reading-in a csv and place every data in their cells but i guess that this is speedy enough. Also try to find-out Excel can share memory with Pb...

BTW. I am only doing this in my very limited spare time...

[andre drabs]

Yes i wonder what link is used.
Imo for real-time stock quotes you'll need to provide data which has been changed, and thus not all data.

The download is from Yahoo (because that is free). The ID is to only download data from tickers in a kind of watch-list. Not all data, that would be endless to download.

Also, it looks you want to program Excel and use Pb for a minor part i guess.

The plan is to use Excel as the display-area while PbWin would be used to download and transfer the data to Excel or to a string-memory-location where it could be read by Excel. For now, i try to find-out things, not yet really programming.

If you ask me you'll need a good internet method to obtain the data and write your own grid to dump the data in. I parse CSV using ADO, parsing with Excel seems a poor mans solution as well. (As for the handwritten parsers). The exe in this zip may give some idea about speed (which is still poor)

The most time-consuming is the download-part. If that is a background process running in his own separate tread then this will cut-down 75% of the waiting-time.

[Michael Mattias]

>So my plan is....

That looks like it has all the steps you need. But it kind of reminds me of batch files or mainframe JCL... there are just so many places something can go wrong that troubleshooting (as in debugging) could be a nightmare.

As far as I can tell there are two schools of thought for this kind of process:
1. Many small, discrete steps in which exactly one thing happens
2. One "big" program which "does it all."

For some reason I'm a "#2" guy. At least now I am. In Ye Olden Dayes (MS-DOS, OS/VS) the limits on memory and the slowness of computers more or less dictated the "#1" approach.

[Houng Chang]

I may be way off here but,

You say it takes Excel 12 seconds to gather the data. How big are these files? Maybe you just need a faster internet pipe?

This 12-seconds or so is using Excel Query to download 3 web pages (totally 500+ symbols, each sheet contains 17*400 data cells) to Excel worksheets.

If one uses URLdownloadtoFile(), the speed is of course way higher.

HYC

[Edwin Knoppert]

Yes i wonder what link is used.
Imo for real-time stock quotes you'll need to provide data which has been changed, and thus not all data.
Iow, i assume a 'Euronext' (Holland) provides some kind of rss or similar to provide changes.

Downloading all stocks is imo very cumbersome.

Also, it looks you want to program Excel and use Pb for a minor part i guess.

If you ask me you'll need a good internet method to obtain the data and write your own grid to dump the data in.
I parse CSV using ADO, parsing with Excel seems a poor mans solution as well.
(As for the handwritten parsers)

The exe in this zip may give some idea about speed (which is still poor):

http://www.hellobasic.com/vd/internet/Yahoo-Historical_Prices.zip

[Neil Croft]

I may be way off here but,

You say it takes Excel 12 seconds to gather the data. How big are these files? Maybe you just need a faster internet pipe?

[andre drabs]

Andre,

With your input, I think your idea is possible:

1. In a PBwin program, define all the tickers. (Editing function can be in this program function)

2. In the same PBwin program, formulate and execute the URL command. Get data into a file. (If get data into array will be even faster.)

3. In the same PBwin program, analyze the data from the file or from the array data.

Question: is YAHOO data "real time" or 15 minutes delay? This operation should be on real time data to make sense.

HYC

The tickers i have them in excel.

So my plan is:
0) excel starts/calls the Pb-program when updates are needed
1) move the tickerlist from excel to the Pb-Program
2) Pb-program download the csv
3) Pb-program transform data from csv to array
4) Pb-program notify the excel-sheet that new data is there
5) Pb-program transfers data to excel on demand from excel
6) Excel confirms data-arrival and close the Pb-program
7) Excel does it's own job now
8) goto 0

This let one free to change the tickers in the excel-sheet and make excel start the update-process (f.e with a click on a button, or on-time-value). The time that excel freezes the user will be only at data-transfer-time and, with the right routines, be very-limited in time.


Some data from Yahoo is RT, other (i think tickers from DJ and S&P) are delayed 15 min.

Anyway, delayed or not, the most important is to cut down on excel's querry-update-time (read user-freezing-time). BTW. Real-Time-Data (for all tickers) is something you cannot find "for free". Even in your yahoo-account you only have very-limitted Real-time quotes.

[Houng Chang]

Andre,

With your input, I think your idea is possible:

1. In a PBwin program, define all the tickers. (Editing function can be in this program function)

2. In the same PBwin program, formulate and execute the URL command. Get data into a file. (If get data into array will be even faster.)

3. In the same PBwin program, analyze the data from the file or from the array data.

Question: is YAHOO data "real time" or 15 minutes delay? This operation should be on real time data to make sense.

HYC

[andre drabs]

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

If You want some more info on how to get Yahoo quotes from excel, have a look at this page (and its links): http://www.gummy-stuff.org/Yahoo-data.htm

I use "kind of" that in excel, the only problem is that it takes to long to update. So, that is why i am thinking the "Pb-download-way" ...

All info can be received in Csv-files, Pb can first download, then convert the info to an array and then pass that aaray back to excel.

In that way, excel only "blocks" for the time the array is transferred.

When i find the right way i will post the code back here.

[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

[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 !!!

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

[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

[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

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

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

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