Reseting hi/low using a macro

M

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?
 
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) :

Code: 
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
          blt.newTick(k);
    }
    void newbar(string symbol, int interval)
    {
          // get most recent full bar
          Bar b = blt[symbol,interval][-1];
          // output high/low to csv/excel file
          sendindicators(symbol,b.High,b.Low);
    }
}

google tradelink project for more info.
 
Quote from 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)

...Any ideas?
More...

DISCLAIMER:

* 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:

Code: 
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.
 
You must log in or register to reply here.
Back
Top