Reseting hi/low using a macro

Discussion in 'Automated Trading' started by minmike, Apr 4, 2011.

  1. minmike


    I'm trying to collect data on hourly highs and lows. I have been using a circular reference to get the H/Ls.

    ie In cell c3, =max("live price",c3)

    I'm trying to use a macro to reset the circular references. when I do it by hand, the formula just resets itself when I click in the cell. I tried recording a macro to click in the cell, but the cell didn't reset. Then I tried cutting and pasting the formula to a different cell and then cutting and pasting it back. I got an error. First time the macro recorder ever returned an error on what it had written. Any ideas?
  2. That is a bit cumbersome to do in excel.

    I believe you'd have to have the macro keep the current high/low somewhere else in the table... then as you update the last price, run the macro to compare the last value to the current high/low using an if statement.

    Here is how you do this in tradelink, fyi (tradelink is free and open source and works with many brokers) :

    public class MyResponse : ResponseTemplate
        BarListTracker blt = new BarListTracker(BarInterval.Hour);
        public MyResponse()
              // here is data we'll output
              Indicators = new string[] { "Symbol", "HourHigh", "HourLow" };
              // handle new bars so we can write out high low data
              blt.GotNewBar+=new SymIntDelegate(newbar);
        override void GotTick(Tick k)
              // build bars from ticks
        void newbar(string symbol, int interval)
              // get most recent full bar
              Bar b = blt[symbol,interval][-1];
              // output high/low to csv/excel file
    google tradelink project for more info.
  3. byteme



    * I don't know or use Excel
    * I'm not sure I understand the problem.

    Could you watch for changes on live price and then trigger an update of C3 when the live price changes?

    Worksheet Module:

    Private Sub Worksheet_Change(ByVal Target as Range)
       If Target.Address = "$A$1" Then
          If Range("A1").Value > Range("C3").Value
             Range("C3").Value = Range("A1").Value
       End If      
    End Sub
    ...where your "live price" would be in cell A1 in this example.

    That's completely untested code but might give you an idea.
  4. minmike


    Thanks guys. I think I figured it out.