K2E Canada Inc
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG
  • Home
  • TRAINING
    • WEBINARS
    • ROAD TO EXCELLENCE ONLINE SEMINAR
    • SEMINARS
    • TECHNOLOGY CONFERENCE
    • INSTRUCTORS
  • NEWSLETTER
    • Signup
  • ABOUT
  • CONTACT
  • BLOG

Excel's STOCKHISTORY Function is Now Available

3/4/2021

 
Recently added to Excel is the new STOCKHISTORY function. You can use this function to retrieve historical prices for stocks as of a given date or range of dates. Read on, and you will learn how to use this feature to query historical securities prices into an Excel workbook.

Introducing STOCKHISTORY

Excel’s STOCKHISTORY function is finally rolling out to subscription-based versions of Excel. Unfortunately, this function is not yet available with perpetual licenses of Excel, such as Excel 2016 and Excel 2019. Presumptively, Microsoft will add the function to the next new version of Excel.

With this feature, you can retrieve historical stock prices for stocks by simply entering a few variables into a formula. Moreover, you can retrieve values for a single date or a range of dates. Further, if you choose a range of dates, you can designate daily, weekly, or monthly intervals. STOCKHISTORY displays date and closing price by default. However, you can optionally choose to show opening price, high price, low price, and volume if desired.

Using STOCKHISTORY

The syntax for using the STOCKHISTORY function is relatively simple, as indicated below. However, note that of the arguments available, only the stock and start_date are required. Thus, a formula using STOCKHISTORY could be as simple as =STOCKHISTORY(“MSFT”,“1/29/2021”). Of course, this formula returns the closing price for a share of Microsoft stock on January 29, 2021.

Additionally, you can create more sophisticated formulas using STOCKHISTORY if your needs require additional information. Specifically, the full syntax of a formula can include all the following items.

STOCKHISTORY(stock, start_date, [end_date],[interval],[headers], [property0], [property1] [property2], [property3], [property4], [property5])
  • stock: The identifier for the financial instrument targeted. This reference can be a ticker symbol or a Stocks data type.
  • start_date: The earliest date for which you want information
  • end_date (optional): The latest date for which you want information
  • interval (optional): Daily (0), Weekly (1), or Monthly (2) interval options for data
  • headers (optional): Specifies if the formula returns additional header rows with the array
  • property0 – property5 (optional): Specifies which information to include in the result, Date (0), Close (1), Open (2), High (3), Low (4), Volume (5).

Extending the previous example, we can create more powerful formulas that use the new function. For instance, we can use the following formula to use the STOCKHISTORY function to generate a listing of closing prices for a range of dates:
=STOCKHISTORY(“MSFT”,“1/1/2020”,“12/31/2020”)
To illustrate, Figure 1 below provides an abbreviated set of results from the formula shown above.
Picture
Figure 1 - Sample Results Using STOCKHISTORY


Another Example of STOCKHISTORY

Next, the ensuing example of the STOCKHISTORY function incorporates optional arguments to add columns for the Open price, High price, Low price, and Volume for each trading interval.

=STOCKHISTORY(“MSFT”, “1/1/2020”, “12/31/2020”,,1,0,1,2,3,4,5)

For example, Figure 2 illustrates an abbreviated set of results using this formula.
Picture
Figure 2 - STOCKHISTORY with Optional Arguments

Of course, you can use STOCKHISTORY results in the same fashion as if you entered the data manually.

Summary

In short, STOCKHISTORY is one of the most widely-anticipated functions added to Excel in recent years. If you have a subscription-based version of Excel, you should already have access to this feature or receive access soon. Importantly, you can use STOCKHISTORY to retrieve stocks’ historical prices and incorporate them into other calculations in your spreadsheets. Therefore, the next time you need to perform research to obtain historical data about a stock, consider using STOCKHISTORY. Most importantly, if you do, you will reduce the amount of time you spend retrieving data.
You can learn more about STOCKHISTORY and other Excel features and functions by participating in a K2E Canada Inc. training class. Additionally, you can learn more about STOCKHISTORY by reading this Microsoft article.

View a Video Tip That Demonstrates STOCKHISTORY


Tommy Stephens

    Categories

    All
    2022
    Accountant
    Accounting Software
    Accounting Solutions
    Adobe
    Advisory
    AI
    Artificial Intelligence
    Automation
    Backup
    Bitcoin
    Blockchain
    Business Automation
    Business Continuity
    Business Intelligence
    Business Management
    Cloud Computing
    Collaborate
    Collaboration
    Colonial
    Computer
    COVID 19
    COVID-19
    CPA
    CRM
    Cryptocurrency
    Customer Relationship Management
    Cybersecurity
    Cyptocurrencies
    Dext
    Doc.It
    Ecommerce
    Emerging Technologies
    Entrepreneurs
    Excel
    Forecasting
    Forecast Sheet
    Google
    Grammarly
    HR
    Internal Controls
    Internal Priorities
    KPIs
    LET
    Links
    Management Reports
    Microsoft 365
    Microsoft Office 2021
    Microsoft Teams
    Office
    Office 365
    Online Shop
    Outsourcing
    Pandemic
    Paperless
    Personal Computer
    PivotTables
    Power Automate
    Power BI
    PowerPoint
    Productivity
    QuickBooks
    Quickbooks Online
    Ransomware
    Receipt Bank
    Remote Work
    RPA
    Sage 50
    Security
    Small Business
    Small Business Accounting
    Small Business Accounting Software
    Stockhistory
    Technologies
    Technology
    Tech Update
    Windows
    Windows 10
    Windows 11
    Word
    Work At Home
    Workflow
    Work From Home
    Xcm
    Zoho
    Zoho CRM

    Authors


    Ward Blatch
    Ward provides consulting and training services as the Managing Director of K2E Canada Inc. He joined K2E Canada in 2005 and is responsible for the Canadian operations of this international consulting group, which provides professional development technology education for accountants across Canada and the US. Ward lives in rural Nova Scotia and can be reached at ward@k2e.ca.

    Tommy Stephens
    Tommy is one of the shareholders in K2 Enterprises, affiliating with the Firm in 2003 and joining as a shareholder in 2017. At K2, Tommy focuses on creating and delivering content and is responsible for many of the Firm's management and marketing functions. Tommy resides in the metro Atlanta area. You may reach him at tommy@k2e.com.

    Randy Johnson
    Randy is a nationally recognized educator, consultant, and writer with over 40 years experience in Strategic Technology Planning, Accounting Software Selection, Paperless, Systems and Network Integration, Business Continuity and Disaster Recovery Planning, Business Development and Management, Process Engineering and outsourced managed services. Randy can be reached at randy@k2e.com


    Bernie Smith
    Bernie coaches businesses to develop meaningful KPIs and present their management information in the clearest possible way to support good decision making. As the owner of Made to Measure KPIs, he has worked with major organisations including HSBC, Airbus, UBS, Barclays, Credit Suisse, Lloyds and many more.

    RSS Feed

Training & Education

Webinars
Road to Excellence Online Seminar
Seminars
Conferences
Instructors

Tech News

K2E Canada's Tech Update Newsletter

More

Privacy Policy
About
Contact

K2E Canada Inc.  |  484 Scarlett Crescent  |  Burlington, ON L7L 5M2  |  (905) 633-9772
© 2023 K2E Canada Inc. ALL RIGHTS RESERVED.