Time Series and Correlation Strategies

Question

I have various time series that I would like to map and present as either a csv file or as data in datatable (.NET) format. These time series are arrays of pairs of time values ​​(in fact, these are objects that contain more than just time and value). Time series can cover different periods of overlap, and some may even have holes (missing values ​​for given time stamps).

For those interested, I use the OPC HDA.NET library to retrieve historical time series from the OPC HDA server.

As a result, the datatable should have one column for each time series in chronological order based on the timestamp column. See the example below:

|-------|-------|-------|-------|-------|
   TIME    TS1     TS2     TS3     TS4
|-------|-------|-------|-------|-------|
    1       X               X       X
|-------|-------|-------|-------|-------|
    2       X       X       X       X
|-------|-------|-------|-------|-------|
    3       X       X               X
|-------|-------|-------|-------|-------|
    4       X       X       X 
|-------|-------|-------|-------|-------|
    5       X       X       X 
|-------|-------|-------|-------|-------|

? "" . , , .

Answer 1

, , :

Dictionary <TimeSeries, Dictionary<DateTime, Value>> dict = new Dictionary<TimeSeries, Dictionary<DateTime, Value>>();

foreach (TimeSeries series in dict.Keys) {

    //table row output code goes here
    Dictionary<DateTime, Value> innerDict = dict[series];
    foreach (DateTime date in innerDict.Keys) {
        Value seriesValueAtTimeT = innerDict[date];
        //table column output code goes here
    }
}

- , , TimeSeries, Value .. .

Answer 2

(, HashSet), ( ).

var distinctDates = allSeries
  .SelectMany(s => s.Values.Select(v => v.Date))
  .Distinct()
  .OrderBy(d => d)
  .ToArray();

var datePositions = distinctDates
  .Select((d,index) => new 
    {
      Date = d,
      Index = index
    }).
  .ToDictionary(x => x.Date, x => x.Index);

"NumberOfSeries" "NumberOfDates". .

var values = new float[allSeries.Length][];
for (var i=0;i<allSeries.Length;i++)
{
  values[i] = new float[distinctDates.Length];
  var currentSerie = allSeries[i];
  foreach(var value in currentSerie.Values)
  {
    var index = datePositions[value.Date];
    values[i][index] = value.Value;
  }      
}

, VisualStudio, . LINQ, .NET( Lokad.Shared.dll). .

, :

• , . , , , .

• Value (..: float double).

• , , " ". - HDF ( .NET), persist time DB ( )

( ).

Answer 3

, . , , , , . , - . , - .: -)

VBA, Excel 2007. .Net.

. , .

Sub GetIndexData ()
Dim cn as ADODB.Connection, cmd As ADODB.Command, rs As ADODB.Recordset
Dim rPivotTopLeft As Range, rPivotBottomRight As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

'Get the data.'
Set cn = New ADODB.Connection
With cn
  .Provider = "SQLOLEDB"
  .ConnectionString = "Database=" & mDBName & ";" & _
                      "Server=" & mDBServerName & ";" & _
                      "UID=" & mDBUserID & ";" & _
                      "Password=" & mDBPassword & ";" & _
                      "Persist Security Info=True;"
  .CursorLocation = adUseClient
  .Open
End With

Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
With cmd
  .ActiveConnection = adoTools.DBConnection
  .CommandType = adCmdText
  .CommandText = "SELECT YourData From YourSource WHERE YourCritera"
  Set rs = .Execute
End With



If Not (rs.BOF And rs.EOF) Then 'Check that we have some data.'

'Put the data into a worksheet.'
With wsRawData
  .Cells.CurrentRegion.Clear

  Set rPivotTopLeft = .Range("A1")
  With ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
    Set .Recordset = rs
    .CreatePivotTable _
        TableDestination:=rPivotTopLeft, _
        TableName:="MyPivotTable"
  End With

  'Massage the data into the desired layout.'
  With .PivotTables("MyPivotTable")
    .ManualUpdate = True

    .PivotFields("Date").Orientation = xlRowField
    .PivotFields("Index").Orientation = xlColumnField
    .AddDataField .PivotFields("Return"), "Returns", xlSum

    .DisplayFieldCaptions = False
    .ColumnGrand = False
    .RowGrand = False

    .ManualUpdate = False
  End With

  mMonthCount = Range(.Range("A3"), .Cells(Rows.Count, "A").End(xlUp)).Count
  mIndexCount = Range(.Range("B2"), .Cells(2, Columns.Count).End(xlToLeft)).Count

  'Convert pivot table to values.'
  Set rPivotBottomRight = .Cells(mMonthCount + 2, mIndexCount + 1)
  With .Range(rPivotTopLeft, rPivotBottomRight)
    .Copy
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  End With

  'Format the worksheet.'
  .Range("A3").Resize(mMonthCount, 1).NumberFormat = "mmm-yy"
  .Range("B3").Resize(mMonthCount, mIndexCount).NumberFormat = "0.00%"
  Union(.Rows(2), .Columns(1)).Font.Bold = True
  .Cells.ColumnWidth = 7.14
  .Rows(1).Delete

End With


rs.close
Set rs = Nothing
cmd.ActiveConnection = Nothing
Set cmd = Nothing
cn.close
Set cn = Nothing

End Sub

excel . 600x600 45 .

, .PivotFields , .

Answer 4

One thing you can do if memory usage becomes a problem is to move from tracking individual events to tabulated event counts for a given time period. You lose some precision in accuracy when this happens, but often summing up your data like this can reduce the complexity of your picture and make trends more obvious.

If this is not obvious, I mean receiving data that looks like this:

12:00 event1
12:01 event2
12:10 event1
12:11 event1

:

12:00-12:15 event1 3
12:00-12:15 event2 1