Streamlining Stock Portfolio Management in Excel (Video) (Course Id 2795)

• Who is this course for?
  This course is designed for CPAs, accountants, and other professionals seeking Continuing Professional Education who have prior experience with Microsoft Excel and want to improve their spreadsheet-based portfolio management skills.

• What is this course about or what problem does this course solve?
  This course teaches how to streamline stock portfolio management by using Microsoft 365 Excel and Google Sheets tools such as stock data types, STOCKHISTORY, Smart Chips, and Power Query to efficiently track, analyze, and manage financial data.

• How can the knowledge from this course be used?
  The knowledge can be used to build live stock tracking tables, integrate real-time financial data, connect to external data sources via APIs, and create dynamic reports using PivotTables and slicers for better financial analysis.

• Why is this course important to a CPA or Accountant?
  This course is important because it enhances a CPA’s or accountant’s ability to manage and analyze investment data efficiently using modern spreadsheet tools, improving accuracy and decision-making in financial reporting and portfolio tracking.

• When is this course relevant or timely?
  This course is relevant for professionals currently working with financial data or investment tracking who want to leverage the latest Excel and Google Sheets features, including Microsoft 365 capabilities and real-time data integration.

• How is a course like this consumed or used?
  This is a QAS self-study video course that is consumed by watching step-by-step demonstrations, following along with provided Excel workbooks, and completing a final exam to earn CPE credit.

In this presentation, author and Excel expert David H. Ringstrom, CPA, will guide you through the essential tools and techniques for effective portfolio management using Microsoft 365 and Google Sheets. Discover how to choose the right tool for your needs by comparing Excel Data Types with Sheets Smart Chips, and learn to enrich your stock tickers with real-time data. David will demonstrate how to build a live stock table using the STOCKHISTORY function, as well as how to track stocks with Google Sheets and link them back to Excel. Additionally, you'll gain insights into leveraging Power Query for connecting files and extracting data via APIs, while also mastering Excel's filtering capabilities with slicers and creating dynamic PivotTables. Join us to enhance your portfolio management skills and take your data analysis to the next level!

David H. Ringstrom, CPA, has more than 30 years of experience as a spreadsheet and accounting software consultant and speaker. He has presented over 2,500 live webinars and is the author or co-author of ten books, including “Microsoft 365 Excel All-in-One for Dummies”, “Microsoft 365 Excel for Dummies”, “Exploring Microsoft Excel’s Hidden Treasures”, and “QuickBooks Online for Dummies”.

In his webinars, David demonstrates every technique twice: first on a PowerPoint slide with numbered steps, and then live in Excel for Microsoft 365 for Windows. He highlights any differences in Excel 2024, 2021, or 2019 during the presentation and in his detailed handouts. Attendees also receive an Excel workbook containing most of the examples he uses, making it easy to follow along and apply the techniques later. David additionally supports Excel for Mac users by answering their follow-up questions via email.

Video - NASBA QAS - NASBA Registry - 2795

Course Learning Objectives

• Explain how to use Excel’s Stocks Data Type to insert related financial fields, such as 52-week high, into a worksheet.
• Recall the Google Sheets function used to retrieve live financial market data such as stock prices.
• Explain how to use Power Query’s From Web option to connect to APIs and import data into Excel.

Chapter 1 - Streamlining Stock Portfolio Management in Excel

Chapter 1 Review Questions

Key Terms