I am trying to improve my stock portfolio spreadsheet functionality by adding an all-time high and low display. Trying but failing. For the life of me, I cannot figure out how to arrange these using formulas. Is it possible or must I resort to VBA?
I am trying to improve my stock portfolio spreadsheet functionality by adding an all-time high and low display. Trying but failing. For the life of me, I cannot figure out how to arrange these using formulas. Is it possible or must I resort to VBA?
It depends on what data you have. If you have a page with a bunch of stock prices that are updated every day, yes, you have to use VBA to capture the price every time it exceeds the previous high/low. If you keep a full history of prices for each stock then it is a trivial use of MAX and MIN.
Can you describe in detail your data and how it is laid out, and how you update it? It would be better if you could just attach the file, or a sanitized version to protect any personal data. See yellow banner at the top of the page.
What I am currently tracking is the overall performance percentage based upon my original investment amount, so its 1 number. I tried using MAX but that didn't give me a static high, only a relative volatile high number. What I want is to be able to capture, automatically, and update each new performance point achieved and to lock in at that point unless a new but higher (or lower) number is reached.
*In the end, I would be tracking two "all-time" numbers from that one performance point.
**Attached is the document, it feeds using the app StockConnector. The boxed cell is the number in question.
Last edited by LeeRedBeard; 05-12-2020 at 02:16 PM.
It appears that you are not doing anything to store historical data here. Something like what I describe here could work for you: https://www.excelforum.com/excel-gen...amic-cell.html
Originally Posted by shg
OK, So, I dont know what happened. I enabled Iterative calculation (max of 1) then set up my circular reference and my all-time high is tracking. For some reason, my MIN function is not working now. It simply says 0.00% and doesn't change!
How can I fix my MIN function to track my All-time low with the home cell having 0 value?
Last edited by LeeRedBeard; 06-01-2020 at 11:04 AM.
As I explained in the other thread(s), I find that an important part of this kind of thing is having a "reset" condition that allows you to assign a "large" value to the min tracking cell before it starts tracking actual minima. It should be obvious that, if this cell starts blank (meaning 0) and all of your values are above 0, that the eternal minimum will be 0. Follow the discussion in the other threads where we talk about "resetting" the minimum cell so it starts at something larger than you will ever see.
I dont know if I am overlooking something or Im just not understanding. Here is what I have so far.
Neither of your All-time cells has a "reset" function as I talked about in the other thread(s), so I would say that you are overlooking the discussions we had about resetting this kind of circular reference.
1) I entered 1 or TRUE in C40 (because it was convenient).
2) I edit B37 to use C40 as a "reset" condition. =IF(C40,1E9,MIN(...)). C40 returns 1billion. 1billion is kind of random -- choose any value that represents a true "I will never see a value larger than this".
3) Enter 0/FALSE in C40 and the B37 then takes the current value of F37 (because the current value in F37 is smaller than 1billion).
I would probably include a similar reset to the formula in B36 so that you can easily reset both values when needed.
Haha, I definitely didn't understand the reset, I assumed it was some technical explanation that was built-in when making the circular reference. I am curious though, how will this arrangement fair if the min hits 0%, will it continue into the negatives or stop?
=MIN(-1,0) returns -1, because -1 is less than 0, so yes, in a situation like this, your min tracker will track minima down to the worst case market crash you will encounter.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks