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.
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.