Are Historical Stock Prices Available using Excel Formulas

Discussion in 'Pre-Sale Questions' started by Bojames, May 18, 2017.

Are Historical Stock Prices Available using Excel Formulas

  1. Bojames

    Bojames New Member

    Joined:
    May 18, 2017
    Messages:
    4
    Likes Received:
    1
    I am specifically interested in entering an Excel (2010) formula that will retrieve the Closing Price of a stock symbol on a specified date. For example: a cell formula would look similar to:

    =GetHistoricalClosingPrice("MSFT", "05/12/2017")

    I see you have Historical Price info, but the way it was demonstrated in the knowledge base, it looks like it will only operate using GUI buttons to specify the input.

    Please advise.
     
    marketxls likes this.
  2. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    894
    Likes Received:
    8
    Hi,

    Yes, they are. We have formulas like following...
    =close_historical("symbol","date")
    =open_historical("symbol","date")
    =high_historical("symbol","date")
    =low_historical("symbol","date")

    Kind regards
    Ankur
     
  3. Bojames

    Bojames New Member

    Joined:
    May 18, 2017
    Messages:
    4
    Likes Received:
    1
    Hello again.

    I just purchased MarketXLS as a replacement for my long-term usage of the free SMF Stock Market Functions add-in developed by Randy Harmelink, available through http://ogres-crypt.com/SMF, where I had been using the currently non-functional function: RCHGetYahooHistory() -- now made inoperative due to recent changes at Yahoo from which the data was being scraped.

    Now, to use MarketXLS instead, I need to figure out how to make a column in Excel that goes from 1 to 1260, representing Trading Days, where 1=Most Recent Trading Day; and 1260=1260 Trading Days ago. Again, these are Trading Days, not Calendar Days, going back from the present day, back to 1260 trading days ago. Next to this column, I want to get the Historical Adjusted Closing Price for a stock using a formula, such as the one you provided earlier: close_historical("symbol","date"), but in this case, I do not have a "date", but instead I use a "relative" Trading Day (an Integer, as described above), to represent its sequential position relative to the most recent Closing Date.

    Is there a direct way of accomplishing this; or possibly a way to get a sequential list of Trade Dates, as described, that would represent this relative Integer sequence as an intermediate step as shown below; or an other suggestion?

    Ideally, the spreadsheet would look similar to this:

    upload_2017-5-20_12-5-31.png

    Thanks for any help you can provide.
     
  4. Eylem Senyuz

    Eylem Senyuz New Member

    Joined:
    May 15, 2017
    Messages:
    10
    Likes Received:
    0
    Hi Bojames, I had the same issue and I fixed it with WORKDAY(start_date, days, [holidays]) function in XL. =WORKDAY(TODAY(),-1,) will deliver one business day before today, similarly, =WORKDAY(TODAY(),-1260,) will deliver 1260 business days before today (7/23/2012). Note that this does not include US holidays. It is sad that Yahoo stopped providing historical pricing - my XL download tool from Yahoo stopped working as well. Hope this helps
     
  5. Bojames

    Bojames New Member

    Joined:
    May 18, 2017
    Messages:
    4
    Likes Received:
    1
    Thanks Eylem - WORKDAY() will work, given an exclusionary list of days the Market is closed. I searched for a comprehensive list of dates that the Markets have been closed over the past 5 years, but failed to find a neat list anywhere. I scraped a number of disparate "holiday" lists from a number of sites and started to clean up a data set, but after a while doing that tedious task I realized I would have to run some QA test on it to make sure it was accurate. (My models are extremely sensitive to single-day closing prices, and accuracy is paramount). With QA looming, it dawned on me that we can obtain the most accuracy by simply generating a list of dates from today() going back 5-years, decremented by 1-day. Then simply run Close_Historical() against each date. The result produces "NA" for any date where the Market was closed, including weekends AND holidays. All together the list of dates having "NA" as a result is then made a member of an "exhaustive" exclusionary list of historical dates to reference when using WORKDAY(). Going forward, since WORKDAY automatically excludes weekends, only the upcoming Market Holiday dates will be necessary to add to this exclusionary list. Problem solved, except for one detail, which may actually be a subject for a new thread but I'll state it here as a derivative of the solution presented, namely performance.

    In my system, I have to look up 1260 historical closing prices prior to processing. Before, when using the aforementioned free SMF Stock Market Functions add-in, a single query was made to the Yahoo site to obtain the results via the RCHGetYahooHistory() function in that system. Now, with MarketXLS, each of the 1260 closing prices represents a query. From what I observed in the process of collecting the "NA" records described earlier, it is an extremely slow process, and may render my system unusable due to the iterative algorithms I run via VBA/Access. I will try to run it and see what's what, but from what I can see, if the 1260 call's to Close_Historical() is already slow; the iterations I do in the processing phase will likely cause the systems to go to its knees. Even if I buffer the first 1260 call's to my local Access database, the first call is still a major blow to my system compared to getting a single recordset as before with SMF using Yahoo.

    Maybe MarketXLS can make a comparable function that duplicates what this massively useful function accomplished. That is, a single query of the historical closing price of a given symbol, for a specified number of closing days (not range of dates). The back-end would have to do the leg-work as I described earlier, to make sure if a request came in for 1260 historical Closing prices (relative to the most recent Closing date, or a specified Closing date); that the appropriate date range would be calculated; and then a single Excel Array could be populated in the return.

    I think this is an important issue for future use of this product. I will update this thread when I have finished the update to my system using the technique described, but unfortunately I predict it will be futile until a single-query result can be obtained.

    I hope MarketXLS reads this to weigh-in on the performance issue.

    Here is a depiction of building an Exclude List for Close_Historical()
    upload_2017-5-21_1-44-34.png
     

    Attached Files:

    #5 Bojames, May 21, 2017
    Last edited: May 21, 2017
  6. Bojames

    Bojames New Member

    Joined:
    May 18, 2017
    Messages:
    4
    Likes Received:
    1
    Sorry MarketXLS -- I found a new API solution that provides the a Close History in a single query... As I do not wish to dis this forum, if anyone is interested in my findings, you may start a direct conversation with me. Thanks.
     
  7. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    894
    Likes Received:
    8
    Hi Bojames,

    We are releasing a version where in with group mode all the calls will be bundled. I am not sure if you know or have tried, but you can already get Historical data in a single call for upto 40 symbols for upto 20 years in history. May be we should have a quick call so I can explain how that works. I think for your usecase you may not need to use =close_historical. (or it could be only use to update previous day's data).
     
  8. rekkiedominee

    rekkiedominee New Member

    Joined:
    Jan 15, 2017
    Messages:
    15
    Likes Received:
    4
    It would be great to have this information - would you mind doing a blog post about it?
     
  9. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    894
    Likes Received:
    8
    Actually, this will be just to make things faster in the background, nothing as such changes for the end users.
     
  10. Rusty

    Rusty New Member

    Joined:
    Apr 28, 2017
    Messages:
    3
    Likes Received:
    0
    "you can already get Historical data in a single call for upto 40 symbols for upto 20 years in history.".... HOW? How is this done?
     
  11. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    894
    Likes Received:
    8
    Hi,

    First populate 40 cells with symbols, then click on Custom Dates, select your dates and click Get Data. You can also choose the granularity of the data.
     

Share This Page