Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching a high and low value
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching a high and low value
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 " wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching a high and low value
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" wrote: 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 " wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching a high and low value
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 "JLGWhiz" wrote: 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" wrote: 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 " wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Caching a high and low value
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) On Fri, 5 Dec 2008 12:21:01 -0800 (PST), wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Caching? | Excel Programming | |||
images caching | Excel Programming | |||
EXCEL CACHING ... | Excel Programming | |||
Linked files - Caching of data | Excel Discussion (Misc queries) | |||
COM Interface and IDE caching | Excel Programming |