Getting Historical Stock Prices in Excel

Discussion in 'Historical Data' started by marketxls, Apr 10, 2017.

Getting Historical Stock Prices in Excel

  1. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,099
    Likes Received:
    10
    MarketXLS allows you to easily get historical stock prices for multiple time periods.

    The options for getting historical pricing are available in the main marketXLS panel as shown below:

    012916_0936_GettingHist1[1].png

    To use these options, simply select up to 10 cells with valid stock symbols and click on these buttons.

    • Daily Minute Data: This will get the minute by minute pricing on today’s date
    • 1 Year Data: This will get daily end of the day close prices of selected stocks for one year period
    • Custom Date: This allows you to select the dates from when you want the data. This data is EOD daily. The data is available for all US stocks for 20 Years. And is updated daily in our database.
    Key features of the historical data are as follows:

    • Covers all stocks trading on NASDAQ, AMEX, NYSE and ARCA.
    • Includes unadjusted and adjusted open, high, low, close, volume.
    • Includes dividend history and split history.
    • Updated at or before 5pm on all trading days
    The historical data will be nicely formatted and indexed on date with each column representing a symbol for easier comparison and further analysis.

    Historical data also includes Dividend and Splits.

    More about Custom Dates

    When you use custom dates to retreive historical stock prices, you have many options for the periodicity for which you want to get the data. You can choose to retrieve dail, weekly, monthly or quarterly stock prices.

    Let’s say you want to retrieve stock prices for Google. Enter the stock symbol in a cell in the spreadsheet (GOOG). Once you’ve entered the stock symbol, click the ‘Custom Dates’ button in the menu. You will be presented with a custom date selection popup window. In this window, select the date range for which you want to get the stock prices. Along with that you can also change the periodicity to Weekly, Monthly, or Quarterly (default is daily). Now press the ‘Get Data’ button and the stock prices will be presented in a new sheet. I selected the time period of 1st January, 2016 to 31st October, 2016 and weekly periodicity. The results are shown below:

    custom-dates-results[1].png

    As you can see, you ow have the weekly historical stock prices for the Google stock. You can even select multiple stock symbols and the resulting table will contain all the inform for all the selected stocks (maximum 10). To present to you the weekly, monthly and quarterly data, we resample the daily data to the new periodicity.
     
    #1 marketxls, Apr 10, 2017
    Last edited: Apr 10, 2017
  2. Rusty

    Rusty New Member

    Joined:
    Apr 28, 2017
    Messages:
    3
    Likes Received:
    0
    I can't seem to get close_historical_x_months_ago() to work. Aren't the arguments just (symbol, number) such as (FB,1) ? I tried using cell references, and just typing the values in - neither worked. Is 'number' just an integer number? (1,2,3...24,etc)
     
  3. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,099
    Likes Received:
    10
    Hi Rusty,

    It will work like this =close_historical_x_months_ago("MSFT",2), but for now its not returning the value I will check this out.
     
  4. JeffTraeger

    JeffTraeger New Member

    Joined:
    Sep 14, 2017
    Messages:
    9
    Likes Received:
    0
    Is there a way to generate the 52 week high and low stock price by year? For example, the high and low stock price for the year 2010
     
  5. JeffTraeger

    JeffTraeger New Member

    Joined:
    Sep 14, 2017
    Messages:
    9
    Likes Received:
    0
    so if I understand correctly I would have to download daily EOD prices, then sort them from largest to smallest and then select the high and low for the year in question. I was hoping there was a function when given the period in question would automatically generate the high and low - much like you can do for a day using "fiftytwo_weekhigh(symbol)" for the trailing 52 weeks.
     
  6. Jay Zylstra

    Jay Zylstra New Member

    Joined:
    Sep 22, 2017
    Messages:
    4
    Likes Received:
    0
    I'm trying to find adjusted historical stock prices for some companies which are no longer publicly traded. I know the Adjusted_Close_Historical function works, because using it with a popular stock like Microsoft returns a result:
    =Adjusted_Close_Historical("MSFT", DATEVALUE("4/3/1998")) gives a value of "17.01".

    But when I try to look up historical prices for a company like Oneida Ltd or Maytag, both of which have since been acquired, I get a value from the function of "NA", even though the date I'm looking up occurs when the companies were still public:
    =Adjusted_Close_Historical("OCQ", DATEVALUE("4/3/1998"))
    =Adjusted_Close_Historical("MYG", DATEVALUE("4/3/1998"))

    What am I doing wrong? Or are historical stock prices only available for companies which are still public now?
     
  7. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,099
    Likes Received:
    10
    Hi Jay,

    Yes, I have checked this and i could not find these symbols in our database. We are working to change data source for historical for more coverage and will update you in case I find these ones in the new set.

    Kind regards
    Ankur
     

Share This Page