Portfolio analysis and optimizationPortfolio ManagementStock investmentsStocks

Stock Portfolio Analysis – Meaning, Usage & Tracking

Written by Ansh Thakkar (Individual Contributor)
Sun Apr 04 2021
Stock Portfolio Analysis
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
Stock Portfolio Analysis

Stock Portfolio Analysis

For the neophytes, let’s start right from the basics.
What is Stock Portfolio Analysis?
Stock Portfolio analysis is the study of an investment portfolio to keep a regular track of the risks and returns provided by the stocks in the Portfolio. It is done to determine the Portfolio’s suitability and aptness concerning the investor’s needs and risk-taking capacity.
It evaluates a given stock of a portfolio /the whole portfolio on a risk-return basis depending on various factors like inflation, deflation, the historical performance of an asset class, time horizon, etc.
What is the Importance of Stock Portfolio Analysis?
The current financial environment is dynamic. The business and economic situations are changing rapidly, and hence, the needs and requirements of the people are also changing accordingly.
A portfolio depends on the following three factors:
Time Horizon
Investment objectives
Risk Tolerance
A change in these three factors or a change in your needs, preferences, requirements, and situations would result in a difference in the Portfolio structure.
Stock Portfolio Analysis using MarketXLS
There is a template in MarketXLS that facilitates stock portfolio analysis. The Stock Portfolio Tracking Spreadsheet (SPTS) enables you to keep track of your investment portfolio.

Stock Portfolio Analysis
All you need to do is enter transactions in the Input_transactions sheet. Enter data only in the cells colored yellow. Everything else is calculated automatically by the spreadsheet once you click on the “Recalculate Portfolio Tracker” button in the Input_transactions sheet. You should not change formulas or values in any other cells in the file.
Supported transaction types are:
1. buy
2. sell
3. div (dividends)
4. split
From the input transactions sheet, you can keep track of the following information:
• Previous units
• Cumulative units
• Transaction Value
• Previous Cost
• Cost of Transaction
• Cumulative Cost
• Avg Stock Price
• Gains/Losses from Sale & Div
• Realized Gain/Loss %
Another sheet of the template contains the information about our Portfolio based on transactions that we have entered in the “Input_Transactions” sheet.


In our example, we have shown 23 positions in our Portfolio.
This sheet shows the information of the stocks in our Portfolio. You don’t have to enter anything on this sheet. It is based on calculations, and it will automatically calculate the information about all the stocks in your Portfolio.
It entails a range of information starting from the sector in which the stock lies to your current position’s market value.
The main focus of an investor would be on the following information:
1. DPS – Dividend per Share
2. Yield on Cost
3. Cost
4. Total Gain/Loss
5. Market Value

Lastly, our template also contains a sheet that provides an overview of the Portfolio’s performance using graphs and diagrams.


The overview shows the performance of the following information:
1. The market value of Stocks in Portfolio
2. Total Gain/Loss per Stock
3. Unrealized Gain/Loss %
4. Total Gain/Loss per Stock on a Closed Position basis

The Bottom Line
MarketXLS is an Excel-based Investment Research Solution for Serious Investors.
We can conclude that the following points should be noted while investing:
Rule 1: Invest considering the time horizon
Rule 2: Invest considering the growth prospects and risk aspects
Rule 3: Analyse the Portfolio regularly for being safe from the changes in the market
Stock Portfolio Analysis is essential for all the investors who have held their positions in the market. MarketXLS facilitates investors to keep track of their portfolios.
Thus, you can easily keep track of the stocks/ positions you have held in your Portfolio using the template provided by MarketXLS.
None of the content published on marketxls.com constitutes a recommendation that any particular security, portfolio of securities, transaction, or investment strategy is suitable for any specific person.
The author is not offering any professional advice of any kind. The reader should consult a professional financial advisor to determine their suitability for any strategies discussed herein.
The article is written to help users collect the required information from various sources deemed to be an authority in their content. The trademarks, if any, are the property of their owners, and no representations are made. All trademarks referenced are the property of their respective owners. Other trademarks and trade names may be used in this document to refer to either the entity claiming the marks and names or their products. MarketXLS disclaims any proprietary interest in trademarks and trade names other than its own or affiliation with the trademark owner.



Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates

Top 100 Gainers Today

Top 100 losers Today

Real gdpReal personal consumption expReal private investmentReal govt expenditureReal net exportsReal exportsReal importsFederal receiptsFederal outlaysFederal surplus or deficitFederal debtReal private investment nonresidentialReal private investment residentialReal potential gdpReal personal incomeReal personal consumption exp monthlyRpce durable goodsRpce nondurable goodsRpce servicesPersonal savings rateMonetary baseCurrency in circulationBank reservesMoney supply m1Money supply m2Sp500DjiaWilshire indexVixFinancial stress indexCorporate bond index aaCorporate bond index bbbFederal funds rateTreasury rate 3mTreasury rate 1yTreasury rate 5yTreasury rate 10yTips 5yTips 10yBond yield aaaBond yield baaMortgage rate 15yMortgage rate 30yUs dollar weighted averageUsdollar to euroUsdollar to poundYuan to usdollarCanadiandollar to usdollarYen to usdollarCpiCpi wo food energyCpi foodCpi energyChain price indexChain price index wo food energyGdp price deflatorPpi final demandPpi finished goodsPpi materialPpi crude goodsPpi final demand wo food energyPpi finished goods wo food energyHouse price indexHouse price index 20cityCrude oil priceGasoline priceNatural gas priceIndustrial productionCapacity utilisationInventoriesSales retail foodVehicle sales light weightManufacture orders durablesManufacture orders capital goodsLoansConsumer credit outstandingCorporate profitsHousing startsBuilding permitsResidential constructionEmployees nonfarmEmployees privateEmployees goods producingEmployees service providingEmployees governmentUnemployment rateInitial cliamsAverage weeks unemployedJob openingsHiresSeparationsQuitsLayoffs dischargesHours of productionHourly earningsReal outputPopulationLabor forceLabor force participation rate
Search for a stock

Top MarketXLS Rank stocks

Rush Enterprises Inc. Class B Common Stock logo

Rush Enterprises Inc. Class B Common Stock

Optionable: No
Market Cap: 2,753 M
Industry: Auto & Truck Dealerships
52 week range    
Booz Allen Hamilton Holding Corporation logo

Booz Allen Hamilton Holding Corporation

Optionable: Yes
Market Cap: 11,592 M
Industry: Business Services
52 week range    
BWX Technologies Inc. logo

BWX Technologies Inc.

Optionable: Yes
Market Cap: 4,846 M
Industry: Aerospace & Defense
52 week range    
Ritchie Bros. Auctioneers Incorporated logo

Ritchie Bros. Auctioneers Incorporated

Optionable: Yes
Market Cap: 6,928 M
Industry: Business Services
52 week range    
Ituran Location and Control Ltd. logo

Ituran Location and Control Ltd.

Optionable: Yes
Market Cap: 504 M
Industry: Communication Equipment
52 week range    
General Mills Inc. logo

General Mills Inc.

Optionable: Yes
Market Cap: 42,311 M
Industry: Packaged Foods
52 week range    
CRA InternationalInc. logo

CRA InternationalInc.

Optionable: Yes
Market Cap: 646 M
Industry: Business Services
52 week range    
ConAgra Brands Inc. logo

ConAgra Brands Inc.

Optionable: Yes
Market Cap: 16,287 M
Industry: Packaged Foods
52 week range    
Agree Realty Corporation logo

Agree Realty Corporation

Optionable: Yes
Market Cap: 5,522 M
Industry: REIT - Retail
52 week range    
UNITIL Corporation logo

UNITIL Corporation

Optionable: Yes
Market Cap: 928 M
Industry: Utilities - Diversified
52 week range    

More Features

Stand with Ukraine

As the situation in Ukraine escalates, many of us in MarketXLS are left with emotions too overwhelming to name. If you’d like to show your support, but aren’t sure how to, we want to help make it easier for you to act.

For any amount donated, we’ll extend your MarketXLS subscription for double of the donated amount. Please send proof of your payment to support@marketxls.com to avail the extention

From all of us at MarketXLS, thank you!