Daily Open / High / Low / Close functions

Discussion in 'Troubleshooting' started by Stephen Vetter, Jul 3, 2017.

Daily Open / High / Low / Close functions

  1. Stephen Vetter

    Stephen Vetter New Member

    Joined:
    Jun 24, 2017
    Messages:
    11
    Likes Received:
    2
    Hi,

    Am a new user working my way through learning curve, considering whether to update some of my models to replace other ways to load data & analyse. Some questions to help me understand - apologies if I haven't found my way to answer sources ...

    For my models I am just looking for end of day data - several related questions.

    Close functions:
    What is difference between PreviousClose(), PreviousClose1(), PreviousClose2()?
    Note that returned values are not consistent with Yahoo data.

    Close_Historical() would seem to be my preferred function if it returned the close on the requested date or the last trading date before the requested date. Instead Close_Historical () returns NA if the requested date falls on a non-trading day.

    Is there an equivalent function to Yahoo's historical close adjusted (for splits & dividends)?


    Open functions:
    Open() always returns dialog box "That function is not valid".

    What is the difference between Open(), Open1(), Open2().
    Open1(), Open2() for any Canadian symbols (e.g. ABX.TO, ACU.V) returns #VALUE. However, High, Low, RSI & MA calculations return values (even if not accurate - see below).


    High & Low functions:
    DaysHigh(), DaysLow() - can you confirm if this retrieves previous day high/low, or does it return intra-day high to date.
    What is difference between DaysHigh(), DaysLow() compared to functions HighLimit(), LowLimit()?

    Open, High & Low equivalent functions to Close_Historical() would be great to have.


    RSI & MA calculations:
    RSI & MA calculations do not seem to match with downloads or my own calculations from other sources (Google/Yahoo via BulkQuotesXL). Do MA calculations include non-trading days in the calculation? Also, note that SimpleMovingAverage(Symbol,200) always returns NA.

    However, I note that Fifty_dayMovingAverage() and TwoHundred_dayMovingAverage() match very closely to my own calculations.


    Kind regards, Stephen Vetter
     
    marketxls likes this.
  2. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    @Stephen Vetter

    Hi Stephen,

    PreviousClose(), PreviousClose1(), PreviousClose2() - The one without any suffix is primary. Others are placeholders. We may get rid of them soon. Previous close should match yahoo or should be very close even though we do not use yahoo.

    Close_Historical () - You may also use this as =CloseHistorical("MSFT",Today()-1), if yesterday was a trading day this should return Previous day's close.

    Is there an equivalent function to Yahoo's historical close adjusted (for splits & dividends)? - Not at the moment, but we will get this in shortly.

    =Open() will become primary, the only idea as to have more than one functions, to make sure the end objective of getting numbers is achieved. So, the ones without any suffix are the mains, if they do not work there are alternatives in order of 1 and 2 and so on...

    DaysHigh(), DaysLow() - are intraday high and low for a specific day.

    Open, High & Low equivalent functions to Close_Historical() would be great to have. - These are already there as follows...

    =Open_Historical("Symbol", Date)
    =Low_Historical("Symbol", Date)
    =High_Historical("Symbol", Date)
    =Volume_Historical("Symbol", Date)

    RSI & MA calculations: - they are fixed in the pre-production will be released shortly and will be fixed.

    However, I note that Fifty_dayMovingAverage() and TwoHundred_dayMovingAverage() match very closely to my own calculations. - Ok, Great.
     
  3. Stephen Vetter

    Stephen Vetter New Member

    Joined:
    Jun 24, 2017
    Messages:
    11
    Likes Received:
    2
    Hi - Thank you for clarifications.

    re. the _Historical functions - thank you for this info. Note that these functions were not include in the "marketxlsfunctions.xlsx" file. Greatly appreciate if you can update this file.

    Kind regards, Stephen
     

Share This Page