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

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

- Rattrapmax6
- 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)

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

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