title: tick by tick to candlestick/OHLC data in C#/.NET
body: <p>My question is based on this premise:
  <a href="https://stackoverflow.com/questions/9340602/group-ohlc-stockmarket-data-into-multiple-timeframes-with-t-sql">Group OHLC-Stockmarket Data into multiple timeframes with T-SQL</a></p>
  
  <p>Like the asker of that question, I aim to construct candlestick data from a SQL source, but entirely in C#. In other words, I want to download the tick by tick data in its entirety to my C# program first, and then manipulate it in the program to get results as follows:</p>
  
  <pre><code>TimeStamp          | Price 
  2012-02-17 15:15:0 | 102  
  2012-02-17 15:16:0 |108   
  2012-02-17 15:17:0 | 101  
  2012-02-17 15:18:0 |105   
  2012-02-17 15:19:0 |107   
  2012-02-17 15:20:0 |115   
  
  Desired Query Result (5 minutes):
  
  Timestamp       |Open|High|Low|Close
  2012-02-15:19:0 |102 |108 |101|107
  2012-02-15:24:0 |115.|....|...|...
  2012-02-15:29:0 |....|....|...|...
  </code></pre>
  
  <p>(reprinted partially from question above)</p>
  
  <p>My code in C# downloads the entire tick by tick data using the relevant connection strings, and stores the data in arrays.</p>
  
  <pre><code>//SQL connection string
  SqlConnection o_sqlConnection = new SqlConnection();
  o_sqlCommand.CommandText = "(see query below)";
  SqlDataReader o_sqlDataReader;
  ...
  //Storage of query result in arrays
  pricetmp = Convert.ToDouble(o_sqlDataReader["Price"]);
              priceList.Add(pricetmp);
  voltmp = Convert.ToDouble(o_sqlDataReader["Volume"]);
              volList.Add(voltmp);
  //along with date, time and stock symbol
  //eventually
  double[] priceArray = price.ToArray();
  DateTime[] timestampArray = timestamp.ToArray();
  ...
  </code></pre>
  
  <p>I need help with writing the part of the code that yields the same results as the SQL query in the answer to the question above, i.e.</p>
  
  <pre><code>(reprinted from the link)
  select  min(timestamp) as Time
  ,       max(Price) as Highest
  ,       min(Price) as Lowest
  ,       min(case when rn_asc = 1 then [Price] end) as first
  ,       min(case when rn_desc = 1 then [Price] end) as Last
  from    (
      select  row_number() over (
                  partition by cast(cast(timestamp as float) * 24 * 12 as int)
                  order by timestamp) as rn_asc
      ,       row_number() over (
                  partition by cast(cast(timestamp as float) * 24 * 12 as int)
                  order by timestamp desc) as rn_desc
      ,       where Stocksymbol = 'abc123'
      ,       *
      from    @t
      ) as SubQueryAlias
  group by
      cast(cast(timestamp as float) * 24 * 12 as int)
  </code></pre>
  
  <p>Note: I've modified the query above to suit my purpose - i.e. convert tick data (rather than shorter timeframe OHLC data) to OHLC data directly.</p>
  
  <p>As a newbie to C#, I would appreciate some advice on how to code a function that mimics the result of the query above.
  Here is what I have so far:</p>
  
  <pre><code>int j = 0; //n is length of data per expected number of OHLC bars
  double[] open = new double[n];
  double[] close = new double[n];
  double[] high = new double[n];
  double[] low = new double[n];
  int i = 0; int ratio = priceData.Length/n;
  while (i &lt; priceData.Length)
  {
      open[i/ratio] = priceData[i];
      close[i/ratio] = priceData[i];
      high[i/ratio] = priceData[i];
      low[i/ratio] = priceData[i];
  
      if (i != -1)
      {
          for (j = i + 1; j &lt; i + ratio; j++)
          {
          if (priceData[j] &gt; high[i/ratio])
              high[i/ratio] = priceData[j];
          if (priceData[j] &lt; low[i/ratio])
              low[i/ratio] = priceData[j];
          close[i/ratio] = priceData[j];
          }
      }
  
      i += ratio;
  }
  //output open, high, low, close
  </code></pre>
  
  <p>How do I select parameters for the n values based on expected timeframes? (1 min, 2 mins, 15 mins etc.) (as is easy to do in the SQL data)? Or more specifically, how do I generalize  this code to input the required timeframe and use it to calculate the OHLC data? And how do I incorporate the use of the DateTime array (which this example code ignores) to sort the data? </p>
  
  <p>This seems easier to do in SQL because the 24 x 12 calculations, which yields 5 minute data, can be modified to other numbers (eg 24 x 4 for 15 minute data, 24 x 30 for 2 minute data, etc.) for different timeframes. But I need to do this entirely in C#.</p>
  
  <p>My tick-by-tick times are generally every 3 milliseconds, but there is no fixed or consistent number of ticks in the data.</p>