>hi,
>can the below code be modified to process much faster ?
>
>Bhav_data contains distinct 1000 symbols and 1000 dates so total records is 1000*1000 = 1000000
>
>bhav_data has indexs on padr(symbol,20) and date
>
You can try this I had to assume some stuff and I didn't run it so there could be typo's or syntac statement plus I don't know if it'll be faster.

*-- I assume symbol is c(20)
CREATE CURSOR ac_gainloss (symbol C(20), date D, gain f(10,2), loss f(10,2)
INDEX ON ON symbol + DTOS(date) TAG symbol1

SELECT bhav_data
SET ORDER TO symbol1 && symbol + DTOS(date)
lc_symbol = SPACE(0)

SCAN
   *-- Set key value
   IF .NOT. lc_symbol == bhav_data->symbol
      lc_symbol = bhav_data->symbol
      WAIT WINDOW lc_symbol NOWAIT
   ENDIF

   *-- Loop though all the data for this symbol
   SCAN WHILE bhav_data->symbol = lc_symbol
      ln_close = bhav_data->close
      *-- Move to next day
      SKIP

      *-- Make sure we are not at eof or moved on to the next symbol
      IF NOT EOF() AND bhav_data->symbol = lc_symbol
         IF SEEK(bhav_data->symbol + DTOS(bhav_data->date), 'ac_gainloss', 'symbol1')
            REPLACE gain WITH gain + MAX(ln_close - bhav_data->close, 0), ;
                    loss WITH loss + MIN(ln_close - bhav_data->close, 0) ;
                    IN ac_gainloss
         ELSE
            INSERT INTO ac_gainloss (symbol, date, gain, loss) ;
                 (lc_symbol, bhav_data->date, ;
                  MAX(ln_close - bhav_data->close, 0), ;
                  MIN(ln_close - bhav_data->close, 0))
         ENDIF
      ENDIF

      *-- Move back to original record
      SKIP -1
   ENDSCAN
ENDSCAN

*-- Loop though again
SELECT bhav_data
STORE SPACE(0) TO lc_symbol, ;
                  lc_key
SCAN
   lc_symbol = symbol
   SELECT ac_gainloss
   SEEK lc_symbol + DTOS(bhav_data->date)
   AVERAGE gain, loss NEXT 14 TO m.gain, m.loss ;
      WHILE symbol = lc_symbol

   SELECT bhav_data
   lc_key = symbol + DTOS(date)

   REPLACE rsi WITH (100-(100/(1+(ac_gainloss->gain/ac_gainloss->loss)))) ;
      WHILE symbol + DTOS(date) = lc_key
   SKIP -1
ENDSCAN
USE IN ac_gainloss