Efficiency suggestions

If you have questions about any aspect of QBasic programming, or would like to help fellow programmers solve their problems, check out this board!

Moderators: Pete, Mods

Post Reply
dbish
Coder
Posts: 19
Joined: Tue Apr 12, 2005 7:37 pm

Efficiency suggestions

Post by dbish » Mon May 09, 2005 3:17 pm

My project has evolved to the point where my base data files are cleaned up and I now need to process daily updates. The update file is comma-delimited - 19 columns and about 140000 rows (this number varies and has been slowly growing). I look at each Ticker Symbol and calculate a weighted average of several fields and then append a single line containing that days data to a file on my hard drive. I generate two sets of updates and paste one to each of two files (name is form TICKER_OI.csv and TICKER_VL.csv). There are approximately 2400 Ticker symbols so a total of 4800 Saved data files. It runs fairly well and takes about 8 minutes (on a Windows XP machine with 768 Mb RAM and 2.4 Ghz CPU). I compile the .bas file into an .exe file using FreeBasic FBIDE v0.3.3a Does using the command line "fbc Optdaily.bas" allow the code to run faster?

I am not a programmer by training and have re-learned this coding effort with much help from the helpful folks on this and other forums. My question is whether any of you sharp-eyed programmers see any obvious changes which would speed up the processing. I am not worried about memory levels - just pure speed.

Any thoughts?

Thanks,

dbish


CODE:
==========================================
OPTION BASE 1

DECLARE SUB TICKERDO (FIRSTTICKERROW AS INTEGER, LASTTICKERROW AS INTEGER)

DEFINT A-Z
DIM OPTIONLINE AS STRING
DIM SHARED WorkingRawArray(1 TO 200000, 1 TO 19) AS STRING
DIM SHARED WorkingFinalArray_OI(1 TO 5000, 1 TO 16) AS STRING
DIM SHARED WorkingFinalArray_VL(1 TO 5000, 1 TO 16) AS STRING
DIM SHARED WorkingLineArray(5000) as STRING
DIM SHARED KILLArray(1000) AS STRING
DIM SHARED FINALROWNUMBER_KILL AS INTEGER
DIM SHARED CalcArray_VL(200000) AS DOUBLE
DIM SHARED CalcArray_OI(200000) AS DOUBLE
DIM FINALROWNUMBER AS INTEGER
DIM CHAR1 AS STRING
DIM FILENAMECURRENT AS STRING
DIM SHARED FIRSTTICKERROW AS INTEGER
DIM SHARED LASTTICKERROW AS INTEGER
DIM CL AS STRING
DIM var_DATE as STRING
DIM var_DAY as STRING
DIM var_MONTH as STRING
DIM var_YEAR as STRING
DIM Shared DATESTRING as STRING

CLS
' var_DATE$=DATE$
' var_DAY$=MID$(var_DATE$,4,2)
' var_MONTH$=LEFT$(var_DATE$,2)
' var_YEAR$=RIGHT$(var_DATE$,4)
' DATESTRING$=var_YEAR$+var_MONTH$+var_DAY$
'FILENAMECURRENT$ = "E:\OptData\Daily\"+DATESTRING$+".csv"
FILENAMECURRENT$ = "E:\OptData\Daily\20050401.csv"
DATESTRING$=MID$(FILENAMECURRENT$,18,8)
PRINT DATESTRING$
PRINT " "

i = 0
OPEN "C:\Temp2\KILL_TICKER.txt" FOR INPUT AS #5
DO WHILE NOT EOF(5)
i = i + 1
LINE INPUT #5, OPTIONLINE$
CHAR1$ = LEFT$(OPTIONLINE$, 1)
IF CHAR1$ = "" THEN EXIT DO
LOOP
CLOSE #5
FINALROWNUMBER_KILL% = i
OPEN "C:\Temp2\KILL_TICKER.txt" FOR INPUT AS #5
FOR i = 1 TO FINALROWNUMBER_KILL%
INPUT #5, KILLArray$(i)
NEXT i
CLOSE #5

OPEN "C:\temp2\TKR_NEW.csv" FOR APPEND AS #10
CLOSE #10
KILL "C:\temp2\TKR_NEW.csv"
OPEN "C:\temp2\TKR_NEW.csv" FOR APPEND AS #10

i = 0
OPEN FILENAMECURRENT$ FOR INPUT AS #1
DO WHILE NOT EOF(1)
i = i + 1
LINE INPUT #1, OPTIONLINE$
CHAR1$ = LEFT$(OPTIONLINE$, 1)
IF CHAR1$ = "" THEN EXIT DO
LOOP
CLOSE #1
FINALROWNUMBER% = i
OPEN FILENAMECURRENT$ FOR INPUT AS #1
FOR i = 1 TO FINALROWNUMBER%
FOR j = 1 TO 19
INPUT #1, WorkingRawArray$(i, j)
NEXT
NEXT
CLOSE #1

PRINT "DONE READING DAILY ARRAY"
PRINT " "

FIRSTTICKERROW% = 1
FOR i = 1 TO FINALROWNUMBER%
IF i = FINALROWNUMBER% THEN
LASTTICKERROW% = i
CALL TICKERDO(FIRSTTICKERROW%, LASTTICKERROW%)
EXIT FOR
END IF
IF WorkingRawArray$(i, 1) <> WorkingRawArray$(i + 1, 1) THEN
LASTTICKERROW% = i
CALL TICKERDO(FIRSTTICKERROW%, LASTTICKERROW%)
FIRSTTICKERROW% = LASTTICKERROW% + 1
END IF
NEXT i
CLOSE #10
CLOSE #20
END

SUB TICKERDO (FIRSTTICKERROW%, LASTTICKERROW%)
DIM FILENAMESAVE_OI AS STRING
DIM FILENAMESAVE_VL AS STRING
DIM CHAR1 AS STRING
DIM CHAR2 AS STRING
DIM CHAR3 AS STRING
DIM CHAR4 AS STRING
DIM CHAR5 AS STRING
DIM CHARLEN AS INTEGER
DIM BID AS SINGLE
DIM ASK AS SINGLE
DIM CSPRICENUM AS SINGLE
DIM TICKER AS STRING
DIM CSPRICE AS STRING
DIM CSPRICE_PREV as STRING
DIM DATERAW as STRING
DIM DATALEN as Integer
DIM DATADATE_N as DOUBLE
DIM DATADATE_S AS STRING
DIM SLASHCOUNT as INTEGER
DIM SLASHARRAY(2) as INTEGER
DIM MONTH_S as STRING
DIM DAY_S as STRING
DIM YEAR_S as STRING
DIM DAY_N as INTEGER
DIM MONTH_N as INTEGER
DIM YEAR_N as INTEGER
DIM OI AS SINGLE
DIM VOL AS SINGLE
DIM CUMCALL_OI_N AS DOUBLE
DIM CUMCALL_OI_S AS STRING
DIM CUMPUT_OI_N AS DOUBLE
DIM CUMPUT_OI_S AS STRING
DIM CUMCALL_VL_N AS DOUBLE
DIM CUMCALL_VL_S AS STRING
DIM CUMPUT_VL_N AS DOUBLE
DIM CUMPUT_VL_S AS STRING
DIM PUTCALL_OI_N as Single
DIM PUTCALL_OI_S as STRING
DIM PUTCALL_VL_N as Single
DIM PUTCALL_VL_S as STRING
DIM MKTCAP_N as SINGLE
DIM MKTCAP_S as STRING
DIM MKTCAP_FacA_N as Single
DIM MKTCAP_FacA_S as String
DIM MKTCAP_FacB_N as Single
DIM MKTCAP_FacB_S as String
DIM MKTCAP_FacC_N as Single
DIM MKTCAP_FacC_S as String
DIM PC_SD_OI_N as SINGLE
DIM PC_SD_OI_S as STRING
DIM PC_SD_OI_FacA_N as Single
DIM PC_SD_OI_FacA_S as String
DIM PC_SD_OI_FacB_N as Single
DIM PC_SD_OI_FacB_S as String
DIM PC_SD_OI_FacC_N as Single
DIM PC_SD_OI_FacC_S as String
DIM PC_SD_VL_N as SINGLE
DIM PC_SD_VL_S as STRING
DIM PC_SD_VL_FacA_N as Single
DIM PC_SD_VL_FacA_S as String
DIM PC_SD_VL_FacB_N as Single
DIM PC_SD_VL_FacB_S as String
DIM PC_SD_VL_FacC_N as Single
DIM PC_SD_VL_FacC_S as String
DIM FACTOR_A as Single
DIM FACTOR_B as Single
DIM FACTOR_C as Single
DIM A as integer
DIM B as integer
DIM C as integer
DIM A_S as STRING
DIM B_S as STRING
DIM C_S as STRING

A%=4
B%=7
C%=10
A_S$=STR$(A%)
B_S$=STR$(B%)
C_S$=STR$(C%)
FACTOR_A!=(2/(A%+1))
FACTOR_B!=(2/(B%+1))
FACTOR_C!=(2/(C%+1))


CUMPUT_OI_N# = 0
CUMCALL_OI_N# = 0
CUMPUT_VL_N# = 0
CUMCALL_VL_N# = 0

FOR i = FIRSTTICKERROW% TO LASTTICKERROW%
BID! = VAL(WorkingRawArray$(i, 11))
ASK! = VAL(WorkingRawArray$(i, 12))
OI! = VAL(WorkingRawArray$(i, 14))
CalcArray_OI#(i) = (OI! * (BID! + ASK!)) / 2
VOL! = VAL(WorkingRawArray$(i, 13))
CalcArray_VL#(i) = (VOL! * (BID! + ASK!)) / 2
IF WorkingRawArray$(i, 6) = "call" THEN
CUMCALL_OI_N# = CUMCALL_OI_N# + CalcArray_OI#(i)
CUMCALL_VL_N# = CUMCALL_VL_N# + CalcArray_VL#(i)
ELSE
CUMPUT_OI_N# = CUMPUT_OI_N# + CalcArray_OI#(i)
CUMPUT_VL_N# = CUMPUT_VL_N# + CalcArray_VL#(i)
END IF
NEXT i

TICKER$ = WorkingRawArray$(LASTTICKERROW%, 1)
CHARLEN% = LEN(TICKER$)
IF CHARLEN% > 4 THEN
CHAR5$ = MID$(TICKER$, 5, 1)
IF CHAR5$ = "/" THEN CHAR5$ = "-"
END IF
IF CHARLEN% > 3 THEN
CHAR4$ = MID$(TICKER$, 4, 1)
IF CHAR4$ = "/" THEN CHAR4$ = "-"
END IF
IF CHARLEN% > 2 THEN
CHAR3$ = MID$(TICKER$, 3, 1)
IF CHAR3$ = "/" THEN CHAR3$ = "-"
END IF
IF CHARLEN% > 1 THEN
CHAR2$ = MID$(TICKER$, 2, 1)
IF CHAR2$ = "/" THEN CHAR2$ = "-"
END IF
CHAR1$ = MID$(TICKER$, 1, 1)
TICKER$ = CHAR1$ + CHAR2$ + CHAR3$ + CHAR4$ + CHAR5$

PRINT TICKER$
FOR i=1 to FINALROWNUMBER_KILL%
If TICKER$=KILLArray$(i) then EXIT SUB
NEXT i

DATARAW$ = MID$(WorkingRawArray$(LASTTICKERROW%, 8), 1, LEN(WorkingRawArray$(LASTTICKERROW%, 8)) - 11)
DATELEN% = LEN(DATARAW$)
SLASHCOUNT = 0
FOR j = 1 TO DATELEN% - 3
IF MID$(DATARAW$, j, 1) = "/" THEN
SLASHCOUNT% = SLASHCOUNT% + 1
SLASHARRAY%(SLASHCOUNT%) = j
END IF
NEXT j
MONTH_S$ = MID$(DATARAW$, 1, SLASHARRAY%(1) - 1)
DAY_S$ = MID$(DATARAW$, SLASHARRAY%(1) + 1, SLASHARRAY%(2) - SLASHARRAY%(1) - 1)
YEAR_S$ = MID$(DATARAW$, SLASHARRAY%(2) + 1, 4)
DAY_N% = VAL(DAY_S$)
MONTH_N% = VAL(MONTH_S$)
YEAR_N% = VAL(YEAR_S$)
DATADATE_N# = (YEAR_N%*10000) + (MONTH_N%*100) + DAY_N%
DATADATE_S$ = STR$(DATADATE_N#)
CSPRICE$ = WorkingRawArray$(LASTTICKERROW%, 2)
CSPRICENUM!=VAL(CSPRICE$)
CUMCALL_OI_S$ = STR$(CUMCALL_OI_N#)
CUMPUT_OI_S$ = STR$(CUMPUT_OI_N#)
PUTCALL_OI_N!=CUMPUT_OI_N#/CUMCALL_OI_N#
If CUMCALL_OI_N#=0 then PUTCALL_OI_N!=1
PUTCALL_OI_S$=STR$(PUTCALL_OI_N!)
CUMCALL_VL_S$ = STR$(CUMCALL_VL_N#)
CUMPUT_VL_S$ = STR$(CUMPUT_VL_N#)
PUTCALL_VL_N!=CUMPUT_VL_N#/CUMCALL_VL_N#
If CUMCALL_VL_N#=0 then PUTCALL_VL_N!=1
PUTCALL_VL_S$=STR$(PUTCALL_VL_N!)

FILENAMESAVE_OI$ = "E:\OptData\OI\"+TICKER$+"_oi.csv"
OPEN FILENAMESAVE_OI$ FOR APPEND AS #1
N = LOF(1)
CLOSE #1
IF N = 0 THEN
OPEN FILENAMESAVE_OI$ FOR APPEND AS #1
PRINT #1, "DATA,INDEX,CS,MKTCAP,MKTCAPMA,CUMCALLOI,CUMPUTOI,PutCall OI,PC SD OI,PC MA OI,MC MA2,MC MA3,INDIC MA2,INDIC MA3,DUMMY1,DUMMY2"
PRINT #1, "DATE,DATE,PRICE,999,"+ C_S$ +",CUMCALLOI,CUMPUTOI,1,2,"+C_S$+","+A_S$+","+B_S$+","+A_S$+","+B_S$+",DUMMY1,DUMMY2"
CLOSE #1
END IF

i=1
OPEN FILENAMESAVE_OI$ FOR INPUT AS #1
DO WHILE NOT EOF(1)
LINE INPUT #1, WorkingLineArray$(i)
IF RTRIM$(WorkingLineArray$(i)) = "" THEN EXIT DO
i=i+1
LOOP
CLOSE #1
FINALROWNUMBER% = i-1

OPEN FILENAMESAVE_OI$ FOR INPUT AS #1
FOR i = 1 TO FINALROWNUMBER%
FOR j = 1 TO 16
INPUT #1, WorkingFinalArray_OI$(i, j)
NEXT
NEXT
CLOSE #1

OPEN FILENAMESAVE_OI$ FOR APPEND AS #1
MKTCAP_N!=CSPRICENUM!*val(WorkingFinalArray_OI$(2,4))
MKTCAP_S$=STR$(MKTCAP_N!)
MKTCAP_FacA_N! = (MKTCAP_N!*FACTOR_A!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,11))*(1-FACTOR_A!))
MKTCAP_FacA_S$ = STR$(MKTCAP_FacA_N!)
MKTCAP_FacB_N! = (MKTCAP_N!*FACTOR_B!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,12))*(1-FACTOR_B!))
MKTCAP_FacB_S$ = STR$(MKTCAP_FacB_N!)
MKTCAP_FacC_N! = (MKTCAP_N!*FACTOR_C!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,5))*(1-FACTOR_C!))
MKTCAP_FacC_S$ = STR$(MKTCAP_FacC_N!)
PC_SD_OI_N! = (PUTCALL_OI_N!-val(WorkingFinalArray_OI$(2,8)))/val(WorkingFinalArray_OI$(2,9))
PC_SD_OI_S$ = STR$(PC_SD_OI_N!)
PC_SD_OI_FacA_N! = (PC_SD_OI_N!*FACTOR_A!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,13))*(1-FACTOR_A!))
PC_SD_OI_FacA_S$ = STR$(PC_SD_OI_FacA_N!)
PC_SD_OI_FacB_N! = (PC_SD_OI_N!*FACTOR_B!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,14))*(1-FACTOR_B!))
PC_SD_OI_FacB_S$ = STR$(PC_SD_OI_FacB_N!)
PC_SD_OI_FacC_N! = (PC_SD_OI_N!*FACTOR_C!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,10))*(1-FACTOR_C!))
PC_SD_OI_FacC_S$ = STR$(PC_SD_OI_FacC_N!)

IF FINALROWNUMBER%=2 Then
MKTCAP_FacA_N! = MKTCAP_N!
MKTCAP_FacA_S$ = STR$(MKTCAP_FacA_N!)
MKTCAP_FacB_N! = MKTCAP_N!
MKTCAP_FacB_S$ = STR$(MKTCAP_FacB_N!)
MKTCAP_FacC_N! = MKTCAP_N!
MKTCAP_FacC_S$ = STR$(MKTCAP_FacC_N!)
PC_SD_OI_FacA_N! = PUTCALL_OI_N!
PC_SD_OI_FacA_S$ = STR$(MKTCAP_FacA_N!)
PC_SD_OI_FacB_N! = PUTCALL_OI_N!
PC_SD_OI_FacB_S$ = STR$(MKTCAP_FacB_N!)
PC_SD_OI_FacC_N! = PUTCALL_OI_N!
PC_SD_OI_FacC_S$ = STR$(MKTCAP_FacC_N!)
End IF

PRINT #1, WorkingRawArray$(LASTTICKERROW%, 8)+","+DATADATE_S$+","+CSPRICE$+","+MKTCAP_S$+","+MKTCAP_FacC_S$+","+CUMCALL_OI_S$+","+CUMPUT_OI_S$+","+PUTCALL_OI_S$+","+PC_SD_OI_S$+","+PC_SD_OI_FacC_S$+","+MKTCAP_FacA_S$+","+MKTCAP_FacB_S$+","+PC_SD_OI_FacA_S$+","+PC_SD_OI_FacB_S$+",DUMMY1,DUMMY2"
CLOSE #1

FILENAMESAVE_VL$ = "E:\OptData\VL\"+TICKER$+"_VL.csv"
OPEN FILENAMESAVE_VL$ FOR APPEND AS #1
N = LOF(1)
CLOSE #1
IF N = 0 THEN
OPEN FILENAMESAVE_VL$ FOR APPEND AS #1
PRINT #1, "DATA,INDEX,CS,MKTCAP,MKTCAPMA,CUMCALLVOL,CUMPUTVOL,PutCall VOL,PC SD VOL,PC MA VOL,MC MA2,MC MA3,INDIC MA2,INDIC MA3,DUMMY1,DUMMY2"
PRINT #1, "DATE,DATE,PRICE,999,"+ C_S$ +",CUMCALLVOL,CUMPUTVOL,1,2,"+C_S$+","+A_S$+","+B_S$+","+A_S$+","+B_S$+",DUMMY1,DUMMY2"
CLOSE #1
END IF

i=1
OPEN FILENAMESAVE_VL$ FOR INPUT AS #1
DO WHILE NOT EOF(1)
i = i + 1
LINE INPUT #1, OPTIONLINE$
CHAR1$ = LEFT$(OPTIONLINE$, 1)
IF CHAR1$ = "" THEN EXIT DO
LOOP
CLOSE #1
FINALROWNUMBER% = i-1
OPEN FILENAMESAVE_VL$ FOR INPUT AS #1
FOR i = 1 TO FINALROWNUMBER%
FOR j = 1 TO 16
INPUT #1, WorkingFinalArray_VL$(i, j)
NEXT
NEXT
CLOSE #1

OPEN FILENAMESAVE_VL$ FOR APPEND AS #1
PC_SD_VL_N! = (PUTCALL_VL_N!-val(WorkingFinalArray_VL$(2,8)))/val(WorkingFinalArray_VL$(2,9))
PC_SD_VL_S$ = STR$(PC_SD_VL_N!)
PC_SD_VL_FacA_N! = (PC_SD_VL_N!*FACTOR_A!)+(val(WorkingFinalArray_VL$(FINALROWNUMBER%,13))*(1-FACTOR_A!))
PC_SD_VL_FacA_S$ = STR$(PC_SD_VL_FacA_N!)
PC_SD_VL_FacB_N! = (PC_SD_VL_N!*FACTOR_B!)+(val(WorkingFinalArray_VL$(FINALROWNUMBER%,14))*(1-FACTOR_B!))
PC_SD_VL_FacB_S$ = STR$(PC_SD_VL_FacB_N!)
PC_SD_VL_FacC_N! = (PC_SD_VL_N!*FACTOR_C!)+(val(WorkingFinalArray_VL$(FINALROWNUMBER%,10))*(1-FACTOR_C!))
PC_SD_VL_FacC_S$ = STR$(PC_SD_VL_FacC_N!)

IF FINALROWNUMBER%=2 Then
PC_SD_VL_FacA_N! = PUTCALL_VL_N!
PC_SD_VL_FacA_S$ = STR$(MKTCAP_FacA_N!)
PC_SD_VL_FacB_N! = PUTCALL_VL_N!
PC_SD_VL_FacB_S$ = STR$(MKTCAP_FacB_N!)
PC_SD_VL_FacC_N! = PUTCALL_VL_N!
PC_SD_VL_FacC_S$ = STR$(MKTCAP_FacC_N!)
End IF

PRINT #1, WorkingRawArray$(LASTTICKERROW%, 8)+","+DATADATE_S$+","+CSPRICE$+","+MKTCAP_S$+","+MKTCAP_FacC_S$+","+CUMCALL_VL_S$+","+CUMPUT_VL_S$+","+PUTCALL_VL_S$+","+PC_SD_VL_S$+","+PC_SD_VL_FacC_S$+","+MKTCAP_FacA_S$+","+MKTCAP_FacB_S$+","+PC_SD_VL_FacA_S$+","+PC_SD_VL_FacB_S$+",DUMMY1,DUMMY2"
CLOSE #1

CSPRICE_PREV$=WorkingFinalArray_OI$(FINALROWNUMBER%,3)
If CSPRICE_PREV$="PRICE" then CSPRICE_PREV$="0.001"

PRINT #10, DATESTRING$+","+TICKER$+","+CSPRICE$+","+CSPRICE_PREV$
END SUB

================================================

User avatar
{Nathan}
Veteran
Posts: 1169
Joined: Thu Aug 19, 2004 6:08 pm
Location: The wetlands of central Ohio, USA
Contact:

Post by {Nathan} » Sun Jul 03, 2005 3:11 pm

For the love of god, use the code tags and we will probably look at it better.
Image

dbish
Coder
Posts: 19
Joined: Tue Apr 12, 2005 7:37 pm

Post by dbish » Sun Jul 03, 2005 7:42 pm

Nathan1993,

I am not sure what you mean by code tags.

The original post was from three months ago and I am using the code in production currently. I was just thinking that there were some glaring errors that someone might spot. I am not a programmer by occupation - just a finance guy trying to solve a problem. I appreciate your replying to this thread. I can understand that it is long and hard to follow.

The moderator might just as well delete it.

thanks

dbish

User avatar
{Nathan}
Veteran
Posts: 1169
Joined: Thu Aug 19, 2004 6:08 pm
Location: The wetlands of central Ohio, USA
Contact:

Post by {Nathan} » Mon Jul 04, 2005 1:03 pm

Hey... I am only 12. You think I got a job with this? But look up under the section: code tags!!! Use them to make your code

Code: Select all

look like this!!!
It is much easier to read and we don't look at code without it (usually).
Image

User avatar
Rattrapmax6
Veteran
Posts: 1055
Joined: Sun Jan 02, 2005 2:11 pm
Location: At my computer
Contact:

Post by Rattrapmax6 » Mon Jul 04, 2005 2:29 pm

There ya go.... Now will ya help, Nathan?

Only thing I know, run it with FreeBasic.. took like half a sec, 8) , how long does it take you?

Code: Select all

OPTION BASE 1 

DECLARE SUB TICKERDO (FIRSTTICKERROW AS INTEGER, LASTTICKERROW AS INTEGER) 

DEFINT A-Z 
DIM OPTIONLINE AS STRING 
DIM SHARED WorkingRawArray(1 TO 200000, 1 TO 19) AS STRING 
DIM SHARED WorkingFinalArray_OI(1 TO 5000, 1 TO 16) AS STRING 
DIM SHARED WorkingFinalArray_VL(1 TO 5000, 1 TO 16) AS STRING 
DIM SHARED WorkingLineArray(5000) as STRING 
DIM SHARED KILLArray(1000) AS STRING 
DIM SHARED FINALROWNUMBER_KILL AS INTEGER 
DIM SHARED CalcArray_VL(200000) AS DOUBLE 
DIM SHARED CalcArray_OI(200000) AS DOUBLE 
DIM FINALROWNUMBER AS INTEGER 
DIM CHAR1 AS STRING 
DIM FILENAMECURRENT AS STRING 
DIM SHARED FIRSTTICKERROW AS INTEGER 
DIM SHARED LASTTICKERROW AS INTEGER 
DIM CL AS STRING 
DIM var_DATE as STRING 
DIM var_DAY as STRING 
DIM var_MONTH as STRING 
DIM var_YEAR as STRING 
DIM Shared DATESTRING as STRING 

CLS 
' var_DATE$=DATE$ 
' var_DAY$=MID$(var_DATE$,4,2) 
' var_MONTH$=LEFT$(var_DATE$,2) 
' var_YEAR$=RIGHT$(var_DATE$,4) 
' DATESTRING$=var_YEAR$+var_MONTH$+var_DAY$ 
'FILENAMECURRENT$ = "E:\OptData\Daily\"+DATESTRING$+".csv" 
FILENAMECURRENT$ = "E:\OptData\Daily\20050401.csv" 
DATESTRING$=MID$(FILENAMECURRENT$,18,8) 
PRINT DATESTRING$ 
PRINT " " 

i = 0 
OPEN "C:\Temp2\KILL_TICKER.txt" FOR INPUT AS #5 
DO WHILE NOT EOF(5) 
i = i + 1 
LINE INPUT #5, OPTIONLINE$ 
CHAR1$ = LEFT$(OPTIONLINE$, 1) 
IF CHAR1$ = "" THEN EXIT DO 
LOOP 
CLOSE #5 
FINALROWNUMBER_KILL% = i 
OPEN "C:\Temp2\KILL_TICKER.txt" FOR INPUT AS #5 
FOR i = 1 TO FINALROWNUMBER_KILL% 
INPUT #5, KILLArray$(i) 
NEXT i 
CLOSE #5 

OPEN "C:\temp2\TKR_NEW.csv" FOR APPEND AS #10 
CLOSE #10 
KILL "C:\temp2\TKR_NEW.csv" 
OPEN "C:\temp2\TKR_NEW.csv" FOR APPEND AS #10 

i = 0 
OPEN FILENAMECURRENT$ FOR INPUT AS #1 
DO WHILE NOT EOF(1) 
i = i + 1 
LINE INPUT #1, OPTIONLINE$ 
CHAR1$ = LEFT$(OPTIONLINE$, 1) 
IF CHAR1$ = "" THEN EXIT DO 
LOOP 
CLOSE #1 
FINALROWNUMBER% = i 
OPEN FILENAMECURRENT$ FOR INPUT AS #1 
FOR i = 1 TO FINALROWNUMBER% 
FOR j = 1 TO 19 
INPUT #1, WorkingRawArray$(i, j) 
NEXT 
NEXT 
CLOSE #1 

PRINT "DONE READING DAILY ARRAY" 
PRINT " " 

FIRSTTICKERROW% = 1 
FOR i = 1 TO FINALROWNUMBER% 
IF i = FINALROWNUMBER% THEN 
LASTTICKERROW% = i 
CALL TICKERDO(FIRSTTICKERROW%, LASTTICKERROW%) 
EXIT FOR 
END IF 
IF WorkingRawArray$(i, 1) <> WorkingRawArray$(i + 1, 1) THEN 
LASTTICKERROW% = i 
CALL TICKERDO(FIRSTTICKERROW%, LASTTICKERROW%) 
FIRSTTICKERROW% = LASTTICKERROW% + 1 
END IF 
NEXT i 
CLOSE #10 
CLOSE #20 
END 

SUB TICKERDO (FIRSTTICKERROW%, LASTTICKERROW%) 
DIM FILENAMESAVE_OI AS STRING 
DIM FILENAMESAVE_VL AS STRING 
DIM CHAR1 AS STRING 
DIM CHAR2 AS STRING 
DIM CHAR3 AS STRING 
DIM CHAR4 AS STRING 
DIM CHAR5 AS STRING 
DIM CHARLEN AS INTEGER 
DIM BID AS SINGLE 
DIM ASK AS SINGLE 
DIM CSPRICENUM AS SINGLE 
DIM TICKER AS STRING 
DIM CSPRICE AS STRING 
DIM CSPRICE_PREV as STRING 
DIM DATERAW as STRING 
DIM DATALEN as Integer 
DIM DATADATE_N as DOUBLE 
DIM DATADATE_S AS STRING 
DIM SLASHCOUNT as INTEGER 
DIM SLASHARRAY(2) as INTEGER 
DIM MONTH_S as STRING 
DIM DAY_S as STRING 
DIM YEAR_S as STRING 
DIM DAY_N as INTEGER 
DIM MONTH_N as INTEGER 
DIM YEAR_N as INTEGER 
DIM OI AS SINGLE 
DIM VOL AS SINGLE 
DIM CUMCALL_OI_N AS DOUBLE 
DIM CUMCALL_OI_S AS STRING 
DIM CUMPUT_OI_N AS DOUBLE 
DIM CUMPUT_OI_S AS STRING 
DIM CUMCALL_VL_N AS DOUBLE 
DIM CUMCALL_VL_S AS STRING 
DIM CUMPUT_VL_N AS DOUBLE 
DIM CUMPUT_VL_S AS STRING 
DIM PUTCALL_OI_N as Single 
DIM PUTCALL_OI_S as STRING 
DIM PUTCALL_VL_N as Single 
DIM PUTCALL_VL_S as STRING 
DIM MKTCAP_N as SINGLE 
DIM MKTCAP_S as STRING 
DIM MKTCAP_FacA_N as Single 
DIM MKTCAP_FacA_S as String 
DIM MKTCAP_FacB_N as Single 
DIM MKTCAP_FacB_S as String 
DIM MKTCAP_FacC_N as Single 
DIM MKTCAP_FacC_S as String 
DIM PC_SD_OI_N as SINGLE 
DIM PC_SD_OI_S as STRING 
DIM PC_SD_OI_FacA_N as Single 
DIM PC_SD_OI_FacA_S as String 
DIM PC_SD_OI_FacB_N as Single 
DIM PC_SD_OI_FacB_S as String 
DIM PC_SD_OI_FacC_N as Single 
DIM PC_SD_OI_FacC_S as String 
DIM PC_SD_VL_N as SINGLE 
DIM PC_SD_VL_S as STRING 
DIM PC_SD_VL_FacA_N as Single 
DIM PC_SD_VL_FacA_S as String 
DIM PC_SD_VL_FacB_N as Single 
DIM PC_SD_VL_FacB_S as String 
DIM PC_SD_VL_FacC_N as Single 
DIM PC_SD_VL_FacC_S as String 
DIM FACTOR_A as Single 
DIM FACTOR_B as Single 
DIM FACTOR_C as Single 
DIM A as integer 
DIM B as integer 
DIM C as integer 
DIM A_S as STRING 
DIM B_S as STRING 
DIM C_S as STRING 

A%=4 
B%=7 
C%=10 
A_S$=STR$(A%) 
B_S$=STR$(B%) 
C_S$=STR$(C%) 
FACTOR_A!=(2/(A%+1)) 
FACTOR_B!=(2/(B%+1)) 
FACTOR_C!=(2/(C%+1)) 


CUMPUT_OI_N# = 0 
CUMCALL_OI_N# = 0 
CUMPUT_VL_N# = 0 
CUMCALL_VL_N# = 0 

FOR i = FIRSTTICKERROW% TO LASTTICKERROW% 
BID! = VAL(WorkingRawArray$(i, 11)) 
ASK! = VAL(WorkingRawArray$(i, 12)) 
OI! = VAL(WorkingRawArray$(i, 14)) 
CalcArray_OI#(i) = (OI! * (BID! + ASK!)) / 2 
VOL! = VAL(WorkingRawArray$(i, 13)) 
CalcArray_VL#(i) = (VOL! * (BID! + ASK!)) / 2 
IF WorkingRawArray$(i, 6) = "call" THEN 
CUMCALL_OI_N# = CUMCALL_OI_N# + CalcArray_OI#(i) 
CUMCALL_VL_N# = CUMCALL_VL_N# + CalcArray_VL#(i) 
ELSE 
CUMPUT_OI_N# = CUMPUT_OI_N# + CalcArray_OI#(i) 
CUMPUT_VL_N# = CUMPUT_VL_N# + CalcArray_VL#(i) 
END IF 
NEXT i 

TICKER$ = WorkingRawArray$(LASTTICKERROW%, 1) 
CHARLEN% = LEN(TICKER$) 
IF CHARLEN% > 4 THEN 
CHAR5$ = MID$(TICKER$, 5, 1) 
IF CHAR5$ = "/" THEN CHAR5$ = "-" 
END IF 
IF CHARLEN% > 3 THEN 
CHAR4$ = MID$(TICKER$, 4, 1) 
IF CHAR4$ = "/" THEN CHAR4$ = "-" 
END IF 
IF CHARLEN% > 2 THEN 
CHAR3$ = MID$(TICKER$, 3, 1) 
IF CHAR3$ = "/" THEN CHAR3$ = "-" 
END IF 
IF CHARLEN% > 1 THEN 
CHAR2$ = MID$(TICKER$, 2, 1) 
IF CHAR2$ = "/" THEN CHAR2$ = "-" 
END IF 
CHAR1$ = MID$(TICKER$, 1, 1) 
TICKER$ = CHAR1$ + CHAR2$ + CHAR3$ + CHAR4$ + CHAR5$ 

PRINT TICKER$ 
FOR i=1 to FINALROWNUMBER_KILL% 
If TICKER$=KILLArray$(i) then EXIT SUB 
NEXT i 

DATARAW$ = MID$(WorkingRawArray$(LASTTICKERROW%, 8), 1, LEN(WorkingRawArray$(LASTTICKERROW%, 8)) - 11) 
DATELEN% = LEN(DATARAW$) 
SLASHCOUNT = 0 
FOR j = 1 TO DATELEN% - 3 
IF MID$(DATARAW$, j, 1) = "/" THEN 
SLASHCOUNT% = SLASHCOUNT% + 1 
SLASHARRAY%(SLASHCOUNT%) = j 
END IF 
NEXT j 
MONTH_S$ = MID$(DATARAW$, 1, SLASHARRAY%(1) - 1) 
DAY_S$ = MID$(DATARAW$, SLASHARRAY%(1) + 1, SLASHARRAY%(2) - SLASHARRAY%(1) - 1) 
YEAR_S$ = MID$(DATARAW$, SLASHARRAY%(2) + 1, 4) 
DAY_N% = VAL(DAY_S$) 
MONTH_N% = VAL(MONTH_S$) 
YEAR_N% = VAL(YEAR_S$) 
DATADATE_N# = (YEAR_N%*10000) + (MONTH_N%*100) + DAY_N% 
DATADATE_S$ = STR$(DATADATE_N#) 
CSPRICE$ = WorkingRawArray$(LASTTICKERROW%, 2) 
CSPRICENUM!=VAL(CSPRICE$) 
CUMCALL_OI_S$ = STR$(CUMCALL_OI_N#) 
CUMPUT_OI_S$ = STR$(CUMPUT_OI_N#) 
PUTCALL_OI_N!=CUMPUT_OI_N#/CUMCALL_OI_N# 
If CUMCALL_OI_N#=0 then PUTCALL_OI_N!=1 
PUTCALL_OI_S$=STR$(PUTCALL_OI_N!) 
CUMCALL_VL_S$ = STR$(CUMCALL_VL_N#) 
CUMPUT_VL_S$ = STR$(CUMPUT_VL_N#) 
PUTCALL_VL_N!=CUMPUT_VL_N#/CUMCALL_VL_N# 
If CUMCALL_VL_N#=0 then PUTCALL_VL_N!=1 
PUTCALL_VL_S$=STR$(PUTCALL_VL_N!) 

FILENAMESAVE_OI$ = "E:\OptData\OI\"+TICKER$+"_oi.csv" 
OPEN FILENAMESAVE_OI$ FOR APPEND AS #1 
N = LOF(1) 
CLOSE #1 
IF N = 0 THEN 
OPEN FILENAMESAVE_OI$ FOR APPEND AS #1 
PRINT #1, "DATA,INDEX,CS,MKTCAP,MKTCAPMA,CUMCALLOI,CUMPUTOI,PutCall OI,PC SD OI,PC MA OI,MC MA2,MC MA3,INDIC MA2,INDIC MA3,DUMMY1,DUMMY2" 
PRINT #1, "DATE,DATE,PRICE,999,"+ C_S$ +",CUMCALLOI,CUMPUTOI,1,2,"+C_S$+","+A_S$+","+B_S$+","+A_S$+","+B_S$+",DUMMY1,DUMMY2" 
CLOSE #1 
END IF 

i=1 
OPEN FILENAMESAVE_OI$ FOR INPUT AS #1 
DO WHILE NOT EOF(1) 
LINE INPUT #1, WorkingLineArray$(i) 
IF RTRIM$(WorkingLineArray$(i)) = "" THEN EXIT DO 
i=i+1 
LOOP 
CLOSE #1 
FINALROWNUMBER% = i-1 

OPEN FILENAMESAVE_OI$ FOR INPUT AS #1 
FOR i = 1 TO FINALROWNUMBER% 
FOR j = 1 TO 16 
INPUT #1, WorkingFinalArray_OI$(i, j) 
NEXT 
NEXT 
CLOSE #1 

OPEN FILENAMESAVE_OI$ FOR APPEND AS #1 
MKTCAP_N!=CSPRICENUM!*val(WorkingFinalArray_OI$(2,4)) 
MKTCAP_S$=STR$(MKTCAP_N!) 
MKTCAP_FacA_N! = (MKTCAP_N!*FACTOR_A!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,11))*(1-FACTOR_A!)) 
MKTCAP_FacA_S$ = STR$(MKTCAP_FacA_N!) 
MKTCAP_FacB_N! = (MKTCAP_N!*FACTOR_B!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,12))*(1-FACTOR_B!)) 
MKTCAP_FacB_S$ = STR$(MKTCAP_FacB_N!) 
MKTCAP_FacC_N! = (MKTCAP_N!*FACTOR_C!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,5))*(1-FACTOR_C!)) 
MKTCAP_FacC_S$ = STR$(MKTCAP_FacC_N!) 
PC_SD_OI_N! = (PUTCALL_OI_N!-val(WorkingFinalArray_OI$(2,8)))/val(WorkingFinalArray_OI$(2,9)) 
PC_SD_OI_S$ = STR$(PC_SD_OI_N!) 
PC_SD_OI_FacA_N! = (PC_SD_OI_N!*FACTOR_A!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,13))*(1-FACTOR_A!)) 
PC_SD_OI_FacA_S$ = STR$(PC_SD_OI_FacA_N!) 
PC_SD_OI_FacB_N! = (PC_SD_OI_N!*FACTOR_B!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,14))*(1-FACTOR_B!)) 
PC_SD_OI_FacB_S$ = STR$(PC_SD_OI_FacB_N!) 
PC_SD_OI_FacC_N! = (PC_SD_OI_N!*FACTOR_C!)+(val(WorkingFinalArray_OI$(FINALROWNUMBER%,10))*(1-FACTOR_C!)) 
PC_SD_OI_FacC_S$ = STR$(PC_SD_OI_FacC_N!) 

IF FINALROWNUMBER%=2 Then 
MKTCAP_FacA_N! = MKTCAP_N! 
MKTCAP_FacA_S$ = STR$(MKTCAP_FacA_N!) 
MKTCAP_FacB_N! = MKTCAP_N! 
MKTCAP_FacB_S$ = STR$(MKTCAP_FacB_N!) 
MKTCAP_FacC_N! = MKTCAP_N! 
MKTCAP_FacC_S$ = STR$(MKTCAP_FacC_N!) 
PC_SD_OI_FacA_N! = PUTCALL_OI_N! 
PC_SD_OI_FacA_S$ = STR$(MKTCAP_FacA_N!) 
PC_SD_OI_FacB_N! = PUTCALL_OI_N! 
PC_SD_OI_FacB_S$ = STR$(MKTCAP_FacB_N!) 
PC_SD_OI_FacC_N! = PUTCALL_OI_N! 
PC_SD_OI_FacC_S$ = STR$(MKTCAP_FacC_N!) 
End IF 

PRINT #1, WorkingRawArray$(LASTTICKERROW%, 8)+","+DATADATE_S$+","+CSPRICE$+","+MKTCAP_S$+","+MKTCAP_FacC_S$+","+CUMCALL_OI_S$+","+CUMPUT_OI_S$+","+PUTCALL_OI_S$+","+PC_SD_OI_S$+","+PC_SD_OI_FacC_S$+","+MKTCAP_FacA_S$+","+MKTCAP_FacB_S$+","+PC_SD_OI_FacA_S$+","+PC_SD_OI_FacB_S$+",DUMMY1,DUMMY2" 
CLOSE #1 

FILENAMESAVE_VL$ = "E:\OptData\VL\"+TICKER$+"_VL.csv" 
OPEN FILENAMESAVE_VL$ FOR APPEND AS #1 
N = LOF(1) 
CLOSE #1 
IF N = 0 THEN 
OPEN FILENAMESAVE_VL$ FOR APPEND AS #1 
PRINT #1, "DATA,INDEX,CS,MKTCAP,MKTCAPMA,CUMCALLVOL,CUMPUTVOL,PutCall VOL,PC SD VOL,PC MA VOL,MC MA2,MC MA3,INDIC MA2,INDIC MA3,DUMMY1,DUMMY2" 
PRINT #1, "DATE,DATE,PRICE,999,"+ C_S$ +",CUMCALLVOL,CUMPUTVOL,1,2,"+C_S$+","+A_S$+","+B_S$+","+A_S$+","+B_S$+",DUMMY1,DUMMY2" 
CLOSE #1 
END IF 

i=1 
OPEN FILENAMESAVE_VL$ FOR INPUT AS #1 
DO WHILE NOT EOF(1) 
i = i + 1 
LINE INPUT #1, OPTIONLINE$ 
CHAR1$ = LEFT$(OPTIONLINE$, 1) 
IF CHAR1$ = "" THEN EXIT DO 
LOOP 
CLOSE #1 
FINALROWNUMBER% = i-1 
OPEN FILENAMESAVE_VL$ FOR INPUT AS #1 
FOR i = 1 TO FINALROWNUMBER% 
FOR j = 1 TO 16 
INPUT #1, WorkingFinalArray_VL$(i, j) 
NEXT 
NEXT 
CLOSE #1 

OPEN FILENAMESAVE_VL$ FOR APPEND AS #1 
PC_SD_VL_N! = (PUTCALL_VL_N!-val(WorkingFinalArray_VL$(2,8)))/val(WorkingFinalArray_VL$(2,9)) 
PC_SD_VL_S$ = STR$(PC_SD_VL_N!) 
PC_SD_VL_FacA_N! = (PC_SD_VL_N!*FACTOR_A!)+(val(WorkingFinalArray_VL$(FINALROWNUMBER%,13))*(1-FACTOR_A!)) 
PC_SD_VL_FacA_S$ = STR$(PC_SD_VL_FacA_N!) 
PC_SD_VL_FacB_N! = (PC_SD_VL_N!*FACTOR_B!)+(val(WorkingFinalArray_VL$(FINALROWNUMBER%,14))*(1-FACTOR_B!)) 
PC_SD_VL_FacB_S$ = STR$(PC_SD_VL_FacB_N!) 
PC_SD_VL_FacC_N! = (PC_SD_VL_N!*FACTOR_C!)+(val(WorkingFinalArray_VL$(FINALROWNUMBER%,10))*(1-FACTOR_C!)) 
PC_SD_VL_FacC_S$ = STR$(PC_SD_VL_FacC_N!) 

IF FINALROWNUMBER%=2 Then 
PC_SD_VL_FacA_N! = PUTCALL_VL_N! 
PC_SD_VL_FacA_S$ = STR$(MKTCAP_FacA_N!) 
PC_SD_VL_FacB_N! = PUTCALL_VL_N! 
PC_SD_VL_FacB_S$ = STR$(MKTCAP_FacB_N!) 
PC_SD_VL_FacC_N! = PUTCALL_VL_N! 
PC_SD_VL_FacC_S$ = STR$(MKTCAP_FacC_N!) 
End IF 

PRINT #1, WorkingRawArray$(LASTTICKERROW%, 8)+","+DATADATE_S$+","+CSPRICE$+","+MKTCAP_S$+","+MKTCAP_FacC_S$+","+CUMCALL_VL_S$+","+CUMPUT_VL_S$+","+PUTCALL_VL_S$+","+PC_SD_VL_S$+","+PC_SD_VL_FacC_S$+","+MKTCAP_FacA_S$+","+MKTCAP_FacB_S$+","+PC_SD_VL_FacA_S$+","+PC_SD_VL_FacB_S$+",DUMMY1,DUMMY2" 
CLOSE #1 

CSPRICE_PREV$=WorkingFinalArray_OI$(FINALROWNUMBER%,3) 
If CSPRICE_PREV$="PRICE" then CSPRICE_PREV$="0.001" 

PRINT #10, DATESTRING$+","+TICKER$+","+CSPRICE$+","+CSPRICE_PREV$ 
END SUB
-Kevin (aka:Rattra)
(x.t.r.GRAPHICS)

dbish
Coder
Posts: 19
Joined: Tue Apr 12, 2005 7:37 pm

Post by dbish » Mon Jul 04, 2005 6:59 pm

Thanks for adding the code tags. I am fairly happy with efficiency. (I have compiled it with FreeBasic) The data file that gets read in has approximately 160,000 lines of comma delimited text. My program loads the whole data file into an array and determines which lines of code are associated with each stock tickers and then culls that data, calculates some simple statistics, and writes it to the ticker-specific file. There are approximately 2500 tickers with 2 variations of specific files for each (total of 5000 file updates). The program is executed on a 2.4 MHz Dell machine with 768 Mb RAM and running Windows XP Home. Whole process takes about 7 minutes.

There is a lot of manipulation going on so I believe that my program runs efficiently but had simply wondered if there were any obvious improvements in syntax (Using For ... Next instead of GoTo directions, etc.) I have tried to incorporate as many techniques as I know (avoiding Gotos where possible, adding the $ or % identifier to variables in the body of the code, etc). Since I am not a programmer I suspect that there are other simple techniques that I missed. I certainly don't expect anybody to actually follow the whole code logic which would take way too much time.

Thanks

dbish

Antoni
Veteran
Posts: 132
Joined: Wed Jun 15, 2005 3:01 pm
Contact:

Post by Antoni » Tue Jul 05, 2005 6:12 am

dbish:
IMHO 160000 records is a size too big to simply load in memory, work and save at the end...If the file grows too big Windows can run out of memory and start paging and the speed fall near 0.

The appropiate file mode for this type of file is RANDOM, where you use fixed length fields and you do your updates directly to disk. It's not as slow as it may appear. Take a look at it...

Post Reply