+ Reply to Thread
Results 1 to 10 of 10

All time high of live data

  1. #1
    Registered User
    Join Date
    04-17-2020
    Location
    North Dakota, USA
    MS-Off Ver
    Office 2013
    Posts
    12

    Unhappy All time high of live data

    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?

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: All time high of live data

    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.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-17-2020
    Location
    North Dakota, USA
    MS-Off Ver
    Office 2013
    Posts
    12

    Re: All time high of live data

    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.
    Attached Files Attached Files
    Last edited by LeeRedBeard; 05-12-2020 at 02:16 PM.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: All time high of live data

    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
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    04-17-2020
    Location
    North Dakota, USA
    MS-Off Ver
    Office 2013
    Posts
    12

    Re: All time high of live data

    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.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: All time high of live data

    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.

  7. #7
    Registered User
    Join Date
    04-17-2020
    Location
    North Dakota, USA
    MS-Off Ver
    Office 2013
    Posts
    12

    Re: All time high of live data

    I dont know if I am overlooking something or Im just not understanding. Here is what I have so far.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: All time high of live data

    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.

  9. #9
    Registered User
    Join Date
    04-17-2020
    Location
    North Dakota, USA
    MS-Off Ver
    Office 2013
    Posts
    12

    Re: All time high of live data

    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?

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: All time high of live data

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 10-17-2018, 09:41 PM
  2. Sum and Count Consecutive High Values from Time Series Data
    By GeoffH1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2018, 04:32 PM
  3. [SOLVED] capture live data at a specified time
    By sherman51 in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 01-04-2018, 05:25 PM
  4. capture live data at a specified time
    By sherman51 in forum Excel General
    Replies: 0
    Last Post: 12-30-2017, 08:36 AM
  5. Vb to force Dynamic charting values to update at same time as live data
    By Willow350 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-15-2016, 10:18 PM
  6. excel live 60 min data real time
    By freak11 in forum Excel General
    Replies: 1
    Last Post: 06-08-2014, 08:20 PM
  7. help cleaning up data and retrieving the time of day for high and low values...
    By yertleturtle in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-17-2014, 04:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1