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
================================================
Efficiency suggestions
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
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
- {Nathan}
- Veteran
- Posts: 1169
- Joined: Thu Aug 19, 2004 6:08 pm
- Location: The wetlands of central Ohio, USA
- Contact:
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
It is much easier to read and we don't look at code without it (usually).
Code: Select all
look like this!!!
-
- Veteran
- Posts: 1055
- Joined: Sun Jan 02, 2005 2:11 pm
- Location: At my computer
- Contact:
There ya go.... Now will ya help, Nathan?
Only thing I know, run it with FreeBasic.. took like half a sec, , how long does it take you?
Only thing I know, run it with FreeBasic.. took like half a sec, , 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)
(x.t.r.GRAPHICS)
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
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
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...
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...