Unit DumpCandles;

{ This unit tries to fill the candles_d and candles_5m tables with data that
  we get from TAL.  We talk directly to the database in this unit because (a)
  no one would have any use for an intermediate form, like a csv file, and (b)
  because there is just so much data we don't want to add any unnecessary steps
  for all that data.

  We had to add the corporate actions table just for this unit.  By default
  TAL gives us data adjusted for splits.  But our alerts have historical
  data which is not adjusted for splits.  So we have to work backwards to get
  back to the original data.

  We compute several days worth of data.  This is in case we miss a day or
  something.  Or in case the data is cleaned up further at some time in the
  future.  Due to the ridiculous amount of data we can't go back that far.
  But the exact amount is a paramter, so if we had to we could do it once fill
  the tables, after adding a new feature or fixing a bug, then go back to just
  a few days worth.

  The large amount of data is really only a problem in the database.  And adding
  AccumulateInsert helped a lot.
}

Interface

  Uses
    DataFormats, AccumulateInsert,
    MySqlSupport;

  Type
    TDumpCandles =
      Class(TObject)
      Private
        CutOff : TDateTime;
        DailyData, IntraDayData : TAccumulateInsert;
        Database : TMySqlConnection;
        Procedure SendData(Data : TAccumulateInsert);
        Procedure NewData(AccumulatedData : TAccumulateInsert; NewData : String);
      Public
        Destructor Destroy;  Override;
        Constructor Create;
        Procedure AddData(Symbol : String;
                          Data1Min, Data1Day : TBarList;
                          CorporateActions : TCorporateActions);
      End;

Implementation

  Uses
    ConfigFile,
    SysUtils, DateUtils, Math;

  /////////////////////////////////////////////////////////////////////////
  // GLOBAL
  /////////////////////////////////////////////////////////////////////////

  Function RestoreOriginalPrices(Bars : TBarList;
                                 CorporateActions : TCorporateActions) : TBarList;
    Var
      Split, Bar : Integer;
    Begin
      Result := Bars;
      If Length(CorporateActions.Splits) > 0 Then
        Try
          SetLength(Bars, Length(Bars));
          For Split := Low(CorporateActions.Splits) To High(CorporateActions.Splits) Do
            With CorporateActions.Splits[Split] Do
              For Bar := Low(Result) To High(Result) Do
                With Result[Bar] Do
                  If StartTime < Date Then
                    Begin
                      Open := Open * SplitFactor;
                      High := High * SplitFactor;
                      Low := Low * SplitFactor;
                      Close := Close * SplitFactor;
                      //Does TAL adjust the volume for splits?  The number of prints?
                    End
        Except
          SetLength(Result, 0)
        End
    End;

  /////////////////////////////////////////////////////////////////////////
  // TDumpCandles
  /////////////////////////////////////////////////////////////////////////

  Destructor TDumpCandles.Destroy;
    Begin
      SendData(DailyData);
      DailyData.Free;
      SendData(IntraDayData);
      IntraDayData.Free;
      Database.Free
    End;

  Constructor TDumpCandles.Create;
    Begin
      CutOff := IncDay(Today, -StrToIntDef(GetConfigValue(RequestOverightDataProgramSection, 'DaysOfHistory'), 21));
      Database := TMySqlConnection.Create(RequestOverightDataProgramSection);
      DailyData := TAccumulateInsert.Create('REPLACE INTO candles_d(symbol, date, open, close)');
      IntraDayData := TAccumulateInsert.Create('REPLACE INTO candles_5m(symbol, end_time, last_price, high, low)')
    End;

  Procedure TDumpCandles.SendData(Data : TAccumulateInsert);
    Begin
      If Not Data.Empty Then
        Database.SendSql(Data.Get)
    End;

  Procedure TDumpCandles.NewData(AccumulatedData : TAccumulateInsert; NewData : String);
    Begin
      AccumulatedData.Add(NewData);
      If AccumulatedData.Full Then
        Database.SendSql(AccumulatedData.Get)
    End;

  Procedure TDumpCandles.AddData(Symbol : String;
                                 Data1Min, Data1Day : TBarList;
                                 CorporateActions : TCorporateActions);
    Function EndTime(StartTime : TDateTime; Minutes : Integer) : TDateTime;
      Begin  { EndTime }
        Result := IncMinute(DateOf(StartTime), Succ(MinuteOfTheDay(StartTime) Div Minutes) * Minutes)
      End;  { EndTime }
    Procedure DumpIntraday(High, Low, Close : Double;
                           EndTime : TDateTime; Minutes : Integer;
                           SendTo : TAccumulateInsert);
      Var
        NewEntry : String;
      Begin  { DumpIntraday }
        If EndTime <> 0.0 Then
          Begin
            Assert((High <> MaxDouble) And (Low <> MaxDouble) And (Close <> MaxDouble));
            NewEntry := '("' + SqlEscape(Symbol) + '", "' + FormatDateTime('yyyy-mm-dd hh:mm:ss', EndTime) + '", ' + FloatToStr(Close) + ', ' + FloatToStr(High) + ', ' + FloatToStr(Low) + ')';
            NewData(SendTo, NewEntry)
          End
      End;  { DumpIntraday }
    Var
      I : Integer;
      LastEndTime, CurrentEndTime : TDateTime;
      LastClose : Double;
      CurrentHigh, CurrentLow : Double;
      CurrentMinutes : Integer;
      NoVolume : Boolean;
    Begin  { TDumpCandles.AddData }
      { By default the data from TAL has been adjusted for splits.  But our
        historical alerts have the original prices.  So update the candle data
        from TAL to show us the prices before the splits. }
      Data1Day := RestoreOriginalPrices(Data1Day, CorporateActions);
      Data1Min := RestoreOriginalPrices(Data1Min, CorporateActions);

      { End of day data is streightforward. }
      For I := Low(Data1Day) To High(Data1Day) Do
        With Data1Day[I] Do
          If StartTime >= CutOff Then
            NewData(DailyData, '("' + SqlEscape(Symbol) + '", "' + FormatDateTime('yyyy-mm-dd', StartTime) + '", ' + FloatToStr(Open) + ', ' + FloatToStr(Close) + ')');

      { Check to see if the intra-day data has any volume.  Normally we would
        not record a candle if there was no volume in that candle.  But there
        are a few odd symbols that do not show volume ever.  I think most
        indicies are like that, although some don't give us ANY intra-day data.
        I'm curious about the print count.  That might be available for some of
        these items with no volume.  I think that some of the indexes update
        approximately once per minute, and each update counts as a print, but
        I'm not sure. }
      NoVolume := True;
      For I := Low(Data1Min) To High(Data1Min) Do
        If Data1Min[I].Volume > 0 Then
          Begin
            NoVolume := False;
            Break
          End;

      { Accumulate the intra-day data.  We read 1 minute candles from TAL.  We
        currently store 5 minute candles in the database.  We've set this up
        so we could easily change it to work with other time frames. }
      LastEndTime := 0;
      LastClose := MaxDouble;
      CurrentLow := MaxDouble;
      CurrentHigh := MaxDouble;
      For I := Low(Data1Min) To High(Data1Min) Do
        With Data1Min[I] Do
          If (StartTime >= CutOff) And (NoVolume Or (Volume > 0)) Then
            Begin
              CurrentMinutes := MinuteOfTheDay(StartTime);
              If (CurrentMinutes >= 6 * 60 + 30) And (CurrentMinutes < 13 * 60) Then
                Begin
                  CurrentEndTime := EndTime(StartTime, 5);
                  If CurrentEndTime <> LastEndTime Then
                    Begin
                      DumpIntraday(CurrentHigh, CurrentLow, LastClose, LastEndTime, 5, IntraDayData);
                      LastEndTime := CurrentEndTime;
                      CurrentHigh := High;
                      CurrentLow := Low
                    End
                  Else
                    Begin
                      CurrentHigh := Max(CurrentHigh, High);
                      CurrentLow := Min(CurrentLow, Low)
                    End;
                  LastClose := Close
                End
            End;
      DumpIntraday(CurrentHigh, CurrentLow, LastClose, LastEndTime, 5, IntraDayData)
    End;  { TDumpCandles.AddData }

End.