Personal Finance

Looking to master your stock portfolio management in Excel? In this easy-to-follow guide, “LIVE Stock Portfolio Dashboard in Excel in 30 Minutes!”, we cover everything you need to know to set up a real-time stock portfolio tracker. From creating a transaction log and calculating returns to breaking down sector and market cap allocations with beautiful pie charts, this tutorial is your go-to resource for all things investing and Excel. Whether you’re a novice or seasoned investor, this video will equip you with the tools to calculate gains, losses, dividends, and more, all in a live, up-to-date Excel dashboard.

💾 *Purchase the file created in this video here:* https://ryanoconnellfinance.com/product/investors-toolkit-live-stock-tracker-in-excel/
📈 Need help with a project? https://ryanoconnellfinance.com/hire-me/

👉 CORRECTION: I made one mistake @15:03. C4 in the denominator (# of shares purchased), should actually be E4 (# of shares sold). You should also remove the double quotes ,””) at the end of the IF statement from that same formula.

👨‍💼 My Freelance Financial Modeling Services:
► Custom financial modeling solutions tailored for your needs: https://ryanoconnellfinance.com/freelance-finance-services/

*Seeking Alpha deals to help manage your portfolio:*
💰 *Get $50 OFF Alpha Picks:* https://ryano.finance/alpha-picks
📈 *Get $159 OFF Seeking Alpha Premium and Alpha Picks Bundle:* https://ryano.finance/seeking-alpha-bundle
🔥 *Get $30 OFF Seeking Alpha Premium:* https://ryano.finance/seeking-alpha

*Get 10% Off Snowball Analytics to help manage your portfolio with code RYAN here:*
https://snowball-analytics.com/register/ryan

Chapters:
0:00 – Overview of the Live Stock Dashboard in Excel
0:39 – Create a Transaction Log
2:47 – Begin Constructing Portfolio Dashboard
4:11 – Retrieve the Sector of Each Stock in the Portfolio
4:46 – Find the Market Cap Size of Each Company
6:22 – Calculate the Percentage Allocated to Each Share
8:22 – Retrieve the Beta of Each Stock
9:13 – Calculate Today’s Return on Each Share
10:31 – Calculate Average Purchase Price Per Share
14:22 – Calculate Average Sale Price Per Share
15:24 – Calculate Total Dividends Received
16:45 – Calculate Realized Gain or Loss on Each Stock
17:37 – Calculate Total Gain or Loss on Each Stock
18:30 – Calculate Unrealized Gain or Loss on Each Stock
19:52 – Calculate Total Portfolio Gains and Losses
22:45 – Conditionally Format Gains & Losses
23:44 – Create Market Cap Size Pie Chart
26:33 – Create Sector Allocation Pie Chart
27:29 – Create Individual Securities Holdings Pie Chart

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC

Ryan O’Connell, CFA, FRM presents “Dividend Discount Model: A Thorough Introduction”. First we walk through the dividend discount model on pen & pad, then we explore how to perform a DDM analysis in Excel to find out how to value dividend stocks.

🎓 *Get 25% Off CFA Courses (Featuring My Videos!) — Use code RYAN25 here:*
👉 https://ryano.finance/cfa

💾 *Download Free Excel File Here:* https://ryanoconnellfinance.com/product/dividend-discount-model-ddm-excel-template/

Chapters:
0:00 – Dividend Discount Model Definition
1:25 – Dividend Discount Model Formula
2:55 – DDM Example Calculation
3:49 – Download Stock Dividend Data from Yahoo Finance
4:22 – Calculate Dividend Growth Rate
5:08 – Calculate Stock Price Using Dividend Discount Model
6:26 – Is the Stock Overvalued or Undervalued?
7:01 – Calculating Required Return Using Excel Solver

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC

Ryan O’Connell, CFA, FRM shows how to build a stock dashboard in Google Sheets with live updating data using Google Finance.

💾 *Purchase the files created in this video here:* https://ryanoconnellfinance.com/product/google-sheets-stock-dashboard/

📈 *See Why I Recommend This Broker:* https://ryano.finance/ibkr-overview

Chapters:
0:00 – Introduction
0:10 – Create a New Google Sheet
1:11 – Setup Column Headers and Format Dashboard
2:12 – Manually Input Your Stock Portfolio Info
3:28 – Color Code Spreadsheet
4:13 – Calculate Company Market Capitalization
6:24 – Calculate the Amount Paid for the Portfolio
7:06 – Get Live Stock Prices In Google Sheets
8:07 – Calculate Allocation of Portfolio
8:43 – Calculate Stock Portfolio Returns
12:03 – Conditional Formatting Based On Gains & Losses
13:47 – Add Individual Stock Allocation Pie Chart
15:17 – Add Pie Charts Based on Market Cap & Sector
19:13 – Stock Portfolio Dashboard in Google Sheets Conclusion

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC

ALTERNATIVE TITLES
“Create a Custom Stock Tracker in Google Sheets”
“DIY Stock Dashboard: How to Build Your Own”
“Track Your Portfolio: A Step-by-Step Guide to a Google Sheets Stock Dashboard”
“Maximize Your Investments with a Google Sheets Stock Tracker”
“Building a Stock Dashboard in Google Sheets | Live Updating Data”
“Build a Stock Dashboard in Google Sheets: The Ultimate Guide”
“Effortlessly Monitor Stocks with this Google Sheets Dashboard Tutorial”
“Get a Bird’s Eye View of Your Investments with a Google Sheets Stock Dashboard”

Ryan O’Connell, CFA, FRM shows how to create a live updating stock portfolio tracker (dashboard) that DOES NOT require an Office 365 subscription or a Microsoft 365 subscription!

💾 *Purchase the files created in this video here:* https://ryanoconnellfinance.com/product/excel-stock-tracker/

📈 *See Why I Recommend This Broker:* https://ryano.finance/ibkr-overview

CHAPTERS:
0:00 – Introduction
0:21 – Create a new Google Sheet document
0:45 – Use the Google Finance function to retrieve stock prices
2:26 – Connect Microsoft Excel to Google Sheets
4:10 – Input stock portfolio information
5:30 – Calculate stock market capitalization size
7:30 – Format to distinguish between manual inputs and formulas
7:57 – Calculate amount paid and current portfolio value
9:48 – Calculate portfolio allocations
10:15 – Calculate gains and losses on portfolio
12:00 – Conditionally format gains and losses
12:59 – Format the stock portfolio dashboard (tracker)
13:40 – Create stock position allocation pie chart
15:10 – Create sector and market cap pie charts
20:07 – Conclusion

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC

Title: Create a Live Stock Portfolio Tracker in Excel | No Microsoft 365 Required

Alternative Titles:
Create a Stock Portfolio Tracker in Excel With Live Prices | No Microsoft 365 Required
Create a Stock Portfolio Dashboard in Excel With Live Prices | No Microsoft 365 Required
Pull Live Stock Prices Into Excel
Pull Live Stock Prices Into Excel Without Microsoft 365
Pull Live Stock Prices Into Excel Without Office 365

If You Invested $100 In Every S&P 500 Stock 20 Years Ago, This is What You’d Have Today.

🔑 Join this channel to get access to perks & support my work: https://www.youtube.com/channel/UCAkyj2N9kd0HtKhCrejsYWQ/join

This video shows the top 10 stocks in the S&P 500 index from 1/2/2003 to 12/13/2022. Data is based on the adjusted close price. Assumes each stock was purchased on 1/2/2003. Made by Ryan O’Connell, CFA, FRM.

Data Sources: Yahoo Finance, Kaggle
Music: Morning Song, Four Seasons: Winter, and William Tell Gallop

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC

Top 10 Stocks Over 20 Years | $100 Invested In Every Stock

If You Invested $100 In Every S&P 500 Stock 11 Years Ago, This is What You’d Have Today

This video shows the top 10 stocks in the S&P 500 index from 1/3/2012 to 12/13/2022. Data is based on the adjusted close price. Assumes each stock was purchased on 1/3/2012. Made by Ryan O’Connell, CFA, FRM.

*This video represents all stocks in the S&P 500 in December 2022 that were public companies at the start of 2012

🔑 Join this channel to get access to perks & support my work: https://www.youtube.com/channel/UCAkyj2N9kd0HtKhCrejsYWQ/join

Data Sources: Yahoo Finance, Kaggle
Music: Morning Song, Four Seasons: Winter, and William Tell Gallop

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC

Top 10 Stocks Over 11 Years | $100 Invested In Every Stock

Ryan O’Connell, CFA, FRM shows how to calculate NPV and IRR in Excel.

🎓 *Get 25% Off CFA Courses (Featuring My Videos!) — Use code RYAN25 here:*
👉 https://ryano.finance/cfa

💾 Download Free Excel File:
► Grab the file from this video here: https://ryanoconnellfinance.com/product/financial-analysis-excel-file-for-npv-and-irr-calculations/

Chapters:
0:00 – Define the Expected Cash Flows
0:51 – Calculate the Present Value (PV) of Each cash Flow
1:46 – Calculate the Net Present Value (NPV) of the Project
2:14 – Calculate Net Present Value (NPV) using NPV() Excel Function
2:38 – Calculate Internal Rate of Return (IRR) Using IRR() Excel Function
2:54 – NPV and IRR Accept or Reject Rule
3:27 – Experimenting With Different Assumptions

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC

Ryan O’Connell, CFA, FRM shows how to create a loan amortization table in Excel.

Chapters:
0:00 – Defining the Attributes of the Loan
1:33 – Creating a Loan Amortization Table in Excel
4:15 – Making Sense Out of the Loan Amortization Table

📈 *See Why I Recommend This Broker:* https://ryano.finance/ibkr-overview

💾 *Download Free Excel File Here:* https://ryanoconnellfinance.com/product/loan-amortization-table-template-in-excel/

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC

“Calculating the Optimal Portfolio in Excel | Portfolio Optimization” by Ryan O’Connell, CFA FRM. This video is based on the Modern Portfolio Theory (MPT) and the Efficient Frontier.

Chapters:
0:00 – Explanation of Assets
0:36 – Expected Return, Standard Deviation, and Weights
1:50 – Enable Data Analysis Toolpak and Solver Toolpak
2:25 – Get Historical Return Data from Yahoo Finance
3:31 – Create a Covariance Matrix
4:58 – Calculate Portfolio Standard Deviation
5:31 – Calculate Sharpe Ratio
6:39 – Find Optimal Portfolio Using Excel Solver

📈 *See Why I Recommend This Broker:* https://ryano.finance/ibkr-overview

💾 *Download Free Excel File Here:* https://ryanoconnellfinance.com/product/optimal-portfolio-calculation-excel-template/

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC

ALTERNATIVE TITLES:
Portfolio Optimization Made Easy with Excel
Optimal Portfolio Management: Excel Techniques Revealed
Building Your Ultimate Investment Portfolio in Excel
Excel Your Investments: A Guide to Optimal Portfolio Creation
Smarter Investing: Master Portfolio Optimization in Excel

Ryan O’Connell, CFA gives an overview of how a beginner can research an ETF using Fidelity.

*Seeking Alpha deals to help manage your portfolio:*
💰 *Get $50 OFF Alpha Picks:* https://ryano.finance/alpha-picks
📈 *Get $159 OFF Seeking Alpha Premium and Alpha Picks Bundle:* https://ryano.finance/seeking-alpha-bundle
🔥 *Get $30 OFF Seeking Alpha Premium:* https://ryano.finance/seeking-alpha

*Get 10% Off Snowball Analytics to help manage your portfolio with code RYAN here:*
https://snowball-analytics.com/register/ryan

*Disclosure: This is not financial advice and should not be taken as such. The information contained in this video is an opinion. Some of the information could be wrong. This channel is owned and operated by Portfolio Constructs LLC. Some of the links above are affiliate links, meaning, at no additional cost to you, I will earn a commission if you click through and make a purchase.