Caching a high and low value

[Rynofasho]

I have a spreadsheet of a stock portfolio that updates in real time
according to performance. I want to be able to create a cell that
will cache the high of the day and the low of the day. I would
certainly assume I have to use macros to do so.

Basically I just have a net place where the net value of the equity
position updates intraday and I want to run the process and begin
caching the high and low for whatever time period.

Can anyone point me in the right direction to get started?

 

[Billy Liddel]

This is a case of the blind leading the blind. I have never done a live
update. But with a portfolio containing one stock. I would create variables
MyHi, and MyLo

Then would need a comparison

If MyHi > than StockVal Or _
MyLo < StockVal then
' do nothing
Elseif MyHi < StockVal then
MyHi = StckVal
elseif Mylo > StockVal then
MyLo = StockVal
end if

The above code is put into a simple sub.

Assuming that a liveupdate creates a Worksheet_Change event you can call the
macro from there.

If not that you could have a Calculation event to call the macro (The sum of
Stock value)

Someone may have a better solution, but that is where I'd start from.

Regards
Peter

 

[JLGWhiz]

Peter, I did a test or two on that principle and the only problem I saw with
it was the starting values of the reporting cells. I believe they might need
to be reset daily to get reliable daily readings.

 

[Billy Liddel]

Yes I thought of that but did not know how to address it. Perhaps Workbook_Open

MyHi = stockVal
MyLo = StockVal

with a loop for each stock of course.


Thanks for pointing it out

Peter

 

[Chip Pearson]

You can use circular references to do this. If you want to track the
maximum value of cell A1 in cell D1, use

=IF(A1="",0,MAX(A1,D1))

If A1 is empty, the result is 0. For any other (numeric) value, D1
will be the largest number ever reached by A1. If A1 then drops in
value, the maximum will persist in D1.

You'll need to enable Iterative Calculations. On the Tools menu,
choose Options then the Calculation tab. There, check the Iteration
box.

For more info about persistent maximums and minimums, see
http://www.cpearson.com/Excel/PersistentMinMax.aspx


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)