|
As an ex- Merrill Lynch broker for a number of years
(sometimes I like to think I've "graduated" to becoming a programmer,
thank you) , I've never lost my fascination with the markets. One of the
most common things investor / programmers need to do in order to test
trading systems and / or graph stock prices is to be able to read historical
stock prices from downloaded text files. Your typical delimited text file
might be, say, a historical price file from Yahoo in the format, "DATE
HIGH LOW CLOSE VOLUME". You may have many of these files, each with
the stock price name plus a ".txt" or ".csv" extension,
all in the same folder.
Often it can be advantageous to be able to read these
files into recordsets because of the ease of sorting, matching, etc. that
the recordset object provides. In this short article I'll illustrate how
very easy it is to create your own "dynamic" text DSN and use
it to read your price or other delimited text files:
<%
Dim Conn, rs
Set Conn = Server.CreateObject("ADODB.Connection")
' Set the Dbq portion of the string to the physical
folder where your price files reside
Conn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" &
_
"Dbq=C:\Projects\AsyncTransfer\Prices;" & _
"Extensions=asc,csv,tab,txt;" & _
"Persist Security Info=False"
Dim sql
sql = "SELECT * from ADBE.txt"
'(implicit recordset creation through execute method)
set rs = conn.execute(sql)
Response.write "<Table><TR BGCOLOR=lightgrey><TH>Date</TH><TH>High</TH><TH>Low</TH><TH>Close</TH><TH>Vol</TH></TR>"
While Not rs.EOF
'here we have stock price text file in format: Date High Low Close Volume
Response.Write "<TR bgcolor=lightblue><TD>" &
rs(0) & "</TD><TD>" & rs(1) _
& "</TD><TD>" & rs(2) & "</TD><TD>"
& rs(3) & "</TD><TD> " & rs(4) &
"</TD></TR>"
rs.MoveNext
Wend
Response.write "</TABLE>"
rs.close
set rs = nothing
conn.close
set conn = nothing
%>
The stock price file for ADOBE looks like this (ADBE.txt):
20001127,72.5226,72.8971,68.1538,68.7155,55
20001128,68.2162,68.341,64.3467,64.6899,6208200
20001129,62.3027,67.7793,62.1622,64.2218,4569900
20001130,62.5991,65.907,62.1622,63.2857,4917500
20001201,65.907,70.2758,63.9098,67.2176,3681500
20001204,67.4048,69.2772,64.5339,67.9665,3439600
20001205,70.463,77.4532,68.4034,76.5794,4799800
20001206,73.5212,75.2687,65.2828,67.0928,8006700
The neat thing
about this is that by simply changing the SQL Statement and supplying
the name of ANY text file that resides in that folder, you can use the
filesystem object (for example) to get a list of the files in the folder,
and then read each file into a separate recordset object by simply looping
through your array of stock price filenames and dynamically changing the
SELECT statement to match on each iteration. In other words, the DSN you've
created applies to the entire folder, not to an individual file. You specify
the filename as if it were a "table" in your SQL statement.
I've left out a lot of the details here for simplicity's sake, but there's
plenty of documentation on this in the MSDN library.
This is, of course, not limited
to stock files. It can be used for any tabular format delimited text.
It can also be used as a way to parse text files and convert them to XML
"on the fly" in Enterprise data transformations and so on.
Peter Bromberg is an independent consultant specializing in distributed .NET solutions
in Orlando and a co-developer of the EggheadCafe.com
developer website. He can be reached at pbromberg@yahoo.com
|