Stock Portfolio Tracker Excel Template

Posted on

In this article, we will be discussing the Stock Portfolio Tracker template that is used to track your stock portfolio’s performance.  

The template will allow entering all Stock transactions in one single table and immediately view the following in the Stock Portfolio Dashboard.

  • Current market value of all your stocks and the Cash balance
  • Portfolio mix by instrument type and industry
  • Today’s market performance and your portfolio performance
  • List of all your stocks with performance data (Quantity, Gain/Loss, % Gain/Loss, etc.)
Stock Portfolio Dashboard Excel Template

The template can handle 7 types of stock transactions  (Buy, Sell, Cash Deposit, Cash Withdrawal, Dividend Payout, Dividend Reinvestment, Fees).

Download Now

Video Demo

How to use the template

The template has two visible sheets

  1. Transactions – where you enter all the stock transactions
  2. Portfolio Dashboard – which is fully automated and displays the Portfolio dashboard

Transactions

In the transactions sheet, we begin by entering the Starting cash balance at the top.

Entering Stock transactions in Excel

Then we can enter each transaction in the table. In the above screenshot, we have entered several transactions.

Enter Transaction date and Ticker symbol of the stock you traded.

If Excel does not recognize your stock symbol, it will pop up this data selector box.

Select-the-stock-from-the-list
Select-the-stock-from-the-list

You can select the stock you are interested in.

Choose the Transaction Type.

There are 7 possible transaction types supported in the template.

Transaction Types in Stock Portfolio Tracker
  1. Buy: When buying a stock – this will reduce the Cash balance and increase the stock quantity and thus your portfolio’s Market Value
  2. Sell: When selling a stock – – this will increase the Cash balance and decrease the stock quantity and thus your portfolio’s Market Value
  3. Cash Deposit: When adding Cash from external accounts (like your personal checking or savings account in bank) into your stock management platform which would then be available to buy stocks
  4. Cash Withdrawal: When you are taking Cash from the Stock Management platform to an external account (like your personal checking or savings account in bank)
  5. Fees: When any fees are applied which reduces money from your Cash balance
  6. Dividend Payout: When Dividend is issued that increases money in your Cash Balance
  7. Dividend Reinvestment: when Dividend is issued that increases stock quantity of the stock that issued Dividend. Cash balance is not impacted.
Sample Stock Transactions

Type in the number of Units and Price of the Stock for the transaction.

Valid? column checks for whether the required information is entered.

For Buy, Sell and Dividend Reinvestment transactions, Ticker symbol and Units field are required.

For Cash Deposit, Cash Withdrawal, Dividend Payout and Fees, Units are not required. Price field is required.

The Valid? column also checks whether the Price of the stock is between the Low and the High for the day.

If you purchased the stock on a discount such as Employee discount, which brings it below the Low price for the day, you can ignore the Valid column.

Amount column calculates the total amount for the transaction.

Cash Balance reflects the running cash balance after each transaction.

For this to be accurate, please enter all transactions in chronological order.

Stock Sales, Cash Deposit and Dividend Payout increase the cash balance, while the Stock Buys, Cash Withdrawal and Fees reduce the cash balance. Dividend Reinvestment does not impact the cash balance.

Portfolio Dashboard

Stock Portfolio Dashboard – Components

On the left side of the Dashboard, the overall summary is presented along with the Stock Portfolio mix by industry.

Stock Portfolio Dashboard Summary in Excel
Stock Portfolio Mix by Industry

On the right side, we have the individual stocks and their performance.

Individual Stocks in Stock Portfolio

Calculations used:

  • Current Quantity = Quantity Bought – Quantity Sold + Quantity Dividend Reinvestment
  • Current Value = Current Quantity * Last Price of Stock
  • Today’s Gain/Loss = Current Value – (Previous Close * Current Quantity)
  • % Today’s Gain/Loss = Today’s Gain/Loss / (Current Value – Today’s Gain/Loss)
  • Total Gain/Loss = Current Value – Invested Amount
  • Invested Amount = Total Amount used to buy the stock – Total Amount used to sell the stock
  • % Total Gain/Loss = Total Gain/Loss / Invested Amount
  • Cash Available = Starting Cash Balance – Total Amount used to Buy Stock – Cash Withdrawal – Fees + Total Amount of Selling stock + Cash Deposit + Dividend Payout

Requirements:

To pull live Stock data from the services you would need either Microsoft 365 Subscription Plans for PC and Web or Excel for the web

Timezone

On the top left you can see the last trade time of the first stock in your list.

The default time that Microsoft provides is UTC.  The offset is set to -7 hrs, to convert to pacific time zone.

You can modify this if you need the time in a different time zone in the hidden Help sheet.

Visit https://savvytime.com/converter/utc to view offset.

How to refresh data?

How to refresh Stocks data
How to refresh Stocks data

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

US Stock Market – Free Stock Screener – Excel Template

Posted on

In this article, we will be discussing the US Stock Market Stock Screener Template that you can use to screen or find stocks based on certain criteria.

Features

The template has the following features built in.

  • 7000+ Stocks and ETFs in the Screener
  • Filter by Exchange, Industry, Market Cap, Type (ETF vs Stock), 52 Week High and 52 Week Low
  • Filter by Price, Change, Change %, Volume, PE
  • Customize attributes on display
  • Sort by attributes
Stock Screener for US Market in Excel
Stock Screener for US Market – Full View – Click to view full screen

Download

Video Demo

How to use the template?

The template is very straight-forward to use. You can use the filters to apply certain criteria and the resulting stocks will appear in the table.

US Stock Market – Free Stock Screener – Components

Attributes

The following attributes are displayed in the table for each Stock

  • Ticker Symbol
  • Volume
  • Price
  • Change
  • Change %
  • PE
  • 52 Week Low (Yes/No)
  • 52 Week High (Yes/No)
  • Market Cap
  • Market Cap Type
  • Instrument Type
  • Industry
  • Exchange

There are also two columns that you can customize according to your preference.

Customize Attributes

29 possible attributes are available in the drop-down. You can choose any to make that display. You may have to format the values in some cases as some are text, some are whole numbers and some are decimals.

Filters

Filters or Slicers available to screen Stocks

Exchange, Industry, Cap type and Instrument type are available to filter.

Market Cap Categories

Market Cap > $200B are labelled as Mega, $10B to $200B are labelled as Large, $2B to $10B are Medium, $300M to $2B are Small, $50M to $300M are Micro and less than $50M are Nano.

52 Week Highs or Lows

You can also use filters available to select stocks that have hit the 52 Week High or Low.

52 week high or Low

Clearing Filters

After applying filters, to clear it, click on the Clear Filter button on the top right of each slicer.

Clearing slicers

You can also use Excel’s native filtering to filter on numeric fields like Price, Volume and Change.

You can choose to filter stocks with price greater than $30, stocks between $45 and $200, stocks less than $100, etc.

When you apply multiple filters (whether using slicers or the table filters option), please note that all the conditions must be satisfied for the stock to appear in the table. The conditions are treated as AND conditions.

Sorting

We use Excel’s native Sorting feature to sort the stocks by criteria.

For example, you can sort by Volume to find the stocks that traded the most by volume (or most active).

Just click on the little down arrow button next to Volume and choose Sort Largest to Smallest.

For another example, you can sort by Change % (descending) to find the stocks that gained the most by %.

How to refresh data?

If you have left Excel idle for a few minutes and would like to refresh the data, click on Refresh All in the Data ribbon.

How to refresh Stocks data
How to refresh Stocks data

How to add new stocks?

To add new stocks, please go to the last row of the table. Type a ticker symbol in the first column of next row.

Add new stocks to Stock Screener

If Excel recognizes the stock symbol, it will auto-populate all the other columns in the row and no more action is needed.

If Excel does not recognize your stock symbol, it will pop up this data selector box.

Select-the-stock-from-the-list
Select-the-stock-from-the-list

You can select the stock you are interested in.

No more changes are needed and from now, the new stock will be used for screening.

Requirements:

To pull live Stock data from the services you would need either Microsoft 365 Subscription Plans for PC and Web or Excel for the web

All the templates in this series can be downloaded from indzara.com/stock-market-templates

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

Two Stocks Comparison Excel Template

Posted on

In this article, we will be discussing the Two Stocks Comparison Template that you can use to compare any two stocks.

The template can assist in

  • Comparing any 2 stocks on current price data as well as several attributes
  • Viewing Price history of the two stocks
  • Customizing the price history by time period (5 days, MTD, 1 Month, 3 Months, 6 Months, YTD, 1 Year, 5 Years) as well as time interval (Daily, Weekly, Monthly)
  • Viewing the price trends of two stocks on same axis or different axis.
Compare Microsoft vs Alphabet Inc. Stocks

Download

Video Demo

How to use the template?

The template automatically pulls data from the Stocks Data type service and displays for analysis.

The user has the following input controls to customize the chart.

Input Controls

Two stocks to compare

Just type in a ticker symbol stock symbol

If Excel does not recognize your stock symbol, it will pop up this data selector box.

Select-the-stock-from-the-list
Select-the-stock-from-the-list

You can select the stock you are interested in.

Period

The template can pull up to 5 years of price history by default. You can choose how much history to display on the chart easily using the slicer buttons.

Period-for-the-Stock-price-history
Period-for-the-Stock-price-history

The options are 5 Days, Month Till Date (MTD), 1 Month, 3 Months, 6 Months, Year Till Date (YTD), 1 Year, 5 Years.

Interval

The template allows price history at 3 different intervals.

Interval-of-Stock-price-history
Interval-of-Stock-price-history

Daily/Weekly/Monthly are the options.

Customize Attributes

There are two attributes that can be compared side by side for the two stocks. You can modify them by choosing from the drop down as shown below.

Two Stocks Comparison - Customize Attributes
Two Stocks Comparison – Customize Attributes

Choose Axis

On the top right above the chart, there is an option to choose whether you would like the two price trends to be plotted on the same axis or different.

Same Axis or Different Axis
Same Axis or Different Axis

When you choose Same axis, if the two stocks price magnitudes are very different, the lower magnitude stock trend will be hard to read. Below is an example between MSFT and GOOG.

Two Stocks Comparison - Microsoft vs Alphabet- Same Axis
Two Stocks Comparison – Microsoft vs Alphabet- Same Axis

When we choose Different Axis, now the trends are easier to read.

Two Stocks Comparison - Microsoft vs Alphabet - Different Axis
Two Stocks Comparison – Microsoft vs Alphabet – Different Axis

Sample Analysis

You can type in various combinations of the above inputs to analyze the stocks according to your needs. Here are some samples of what can be done using this template.

Ford vs Ferrari

Ford vs Ferrari – Stocks Comparison

Microsoft vs Apple

Microsoft vs Apple – Stocks Comparison

Mattel vs Hasbro

Hasbro vs Mattel- Stocks Comparison

Mastercard vs Visa

Mastercard vs Visa – Stocks Comparison

Facebook vs Twitter

Facebook vs Twitter – Stocks Comparison

Southwest Airlines vs American Airlines

Southwest Airlines vs American Airlines – Stocks Comparison

The template will work for non-US stocks as well. Here are some examples of Indian stocks.

Tata Communications vs Reliance Communications

Tata Communications vs Reliance Communications – Stocks Comparison

Wipro vs Infosys

Wipro vs Infosys – Stocks Comparison

Requirements:

To pull live Stock data from the services you would need either Microsoft 365 Subscription Plans for PC and Web or Excel for the web

Timezone

Last Trade Time

On the top right you can see the maximum of the last trade time of these stocks.

The default time that Microsoft provides is UTC.  The offset is set to -7 hrs, to convert to pacific time zone.

You can modify this if you need the time in a different time zone.

Visit https://savvytime.com/converter/utc to view offset

How to refresh data?

If you have left Excel idle for a few minutes and would like to refresh the data, click on Refresh All in the Data ribbon.

How to refresh Stocks data
How to refresh Stocks data

All the templates in this series can be downloaded from indzara.com/stock-market-templates

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

India Stock Market – Industry Stocks Analyzer

Posted on

In this article, we will be discussing the India Market – Industry Analyzer template that you can use to analyze stocks.

The template can assist in

  • Analysis of the stocks across any two measures (out of 16 measures available) – for example, Price and Market Cap, Trading Volume and Price – in a scatter plot
  • Analysis of stocks in any specific industry – for example, Telecommunications industry, Pharmaceuticals industry, etc.
  • Comparing two or more specific stocks across various measures
India Stock Market Analyzer

Download

Video Demo

How to use the template?

The template automatically creates a scatter plot of 1663 stocks in the India Stock market, across two measures. The user has the following input controls to customize the chart.

There are six input controls available.

Input controls

X Axis Measure

Y Axis Measure

Measures drop down

The following 16 measures are available for analysis.

  1. Price
  2. Change
  3. Change (%)
  4. High
  5. Low
  6. Open
  7. Previous Close
  8. 52 week high
  9. 52 week low
  10. Employees
  11. Market cap
  12. P/E
  13. Beta
  14. Shares Outstanding
  15. Volume
  16. Volume average

Label

Toggle between Ticker Symbol and Name of the Stock)

Label selection

When you have a lot of stocks in the chart, the ticker symbol will be better to use as it will be less cluttered on the chart. When you have only a few stocks to analyze, the Name option is better as it clearly displays the company name. If you know your ticker symbols very well, then you can stick with the Ticker Symbol itself.

Exchange filter

Exchange Filter

You can filter to specific exchange(s) if you would like.

Industry filter

Industry Filter

You can select one or multiple industries for analysis

Ticker Symbol filter

Ticker Symbol Filter

Finally, you can choose one or multiple stock ticker symbols.

Sample Analysis

Just use various combinations of these inputs to analyze the stocks according to your needs. Here are some samples of what can be done using this template.

  1. Of all 1663 stocks, Price vs Change
Price vs Change
  • Natural Gas Utilities industry – Price vs Employees
Natural Gas – Price vs Employees
  • Oil & Gas industry – Market Cap vs Employees
Market Cap vs Employees
  • Reliance and Airtel – Price vs Employees
Reliance and Airtel – Price vs Employees
  • Software industry stocks – Volume vs Shares Outstanding
Volume vs Shares Outstanding
  • Specific Software industry stocks – Price vs P/E
Price vs P/E

Requirements:

To pull live Stock data from the services you would need either Microsoft 365 Subscription Plans for PC and Web or Excel for the web

How to add a new stock:

The 1663 of the stocks or the ticker symbols in the NSE and BSE exchanges are included. There are a few that are not in the template since the market data is not available in the Excel service.

You can also add your own stocks and I will explain how that can be done easily.  

Unhide the hidden DATA sheet and follow this simple two-step process to add new stocks to analyze.

How to add new stocks
  1. Extend the table to more rows as needed
  2. Enter stock symbol

If Excel does not recognize your stock symbol, it will pop up this data selector box.

You can select the stock you are interested in.

Timezone

On the top right you can see the maximum of the last trade time of these currencies.

The default time that Microsoft provides is UTC.  The offset is set to 5.5 hours, to convert to Indian Standard Time zone.

You can modify this if you need the time in a different time zone.

Visit https://savvytime.com/converter/utc to view offset

How to refresh data?

How to refresh Stocks data
How to refresh Stocks data

For more templates in this series, please visit indzara.com/stock-market-templates

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

US Stock Market – Industry Stocks Analyzer

Posted on

In this article, we will be discussing the US Market – Industry Analyzer template that you can use to analyze stocks.

The template can assist in

  • Analysis of the stocks across any two measures (out of 16 measures available) – for example, Price and Market Cap, Trading Volume and Price – in a scatter plot
  • Analysis of stocks in any specific industry – for example, Telecommunications industry, Pharmaceuticals industry, etc.
  • Comparing two or more specific stocks across various measures
US Stock Market Analyzer Excel Template

Download

Video Demo

How to use the template?

The template automatically creates a scatter plot of 529 stocks in the US Stock market, across two measures.

The user has the following 6 input controls to customize the chart.

Input Controls

X Axis Measure

Y Axis Measure

Measures drop down

The following 16 measures are available for analysis.

  1. Price
  2. Change
  3. Change (%)
  4. High
  5. Low
  6. Open
  7. Previous Close
  8. 52 week high
  9. 52 week low
  10. Employees
  11. Market cap
  12. P/E
  13. Beta
  14. Shares Outstanding
  15. Volume
  16. Volume average

Label

Toggle between Ticker Symbol and Name of the Stock)

Label selection

When you have a lot of stocks in the chart, the ticker symbol will be better to use as it will be less cluttered on the chart. When you have only a few stocks to analyze, the Name option is better as it clearly displays the company name. If you know your ticker symbols very well, then you can stick with the Ticker Symbol itself.

Exchange filter

Exchange Filter

You can filter to specific exchange(s) if you would like.

Industry filter

Industry Filter

You can select one or multiple industries for analysis

Ticker Symbol filter

Ticker Symbol Filter

Finally, you can choose one or multiple stock ticker symbols.

Sample Analysis

You can use various combinations of these inputs to analyze the stocks according to your needs. Here are some samples of what can be done using this template.

  • Of all 529 stocks, Price vs Change %
All Stocks – Price vs Change %
  • Pharmaceuticals industry – Price vs P/E
Pharma Industry – Price vs P/E
  • Passenger Transportation Services industry – Price vs Volume
Passenger Transportation Services industry – Price vs Volume
  • Starbucks and McDonald’s – Price vs Employees
Starbucks and McDonald’s – Price vs Employees
  • Mastercard and Visa – Employees vs Market Cap
Mastercard and Visa – Employees vs Market Cap

Requirements:

To pull live Stock data from the services you would need either Microsoft 365 Subscription Plans for PC and Web or Excel for the web

How to add a new stock:

The stocks or the ticker symbols in the Dow Jones, S&P 500 and Nasdaq 100 are included. You can also add your own stocks and I will explain how that can be done easily. 

Unhide the hidden DATA sheet and follow this simple two-step process to add new stocks to analyze.

How to add new stocks
  1. Extend the table to more rows as needed
  2. Enter stock symbol

If Excel does not recognize your stock symbol, it will pop up this data selector box.

You can select the stock you are interested in.

Timezone

Last Trade Time

On the top right you can see the maximum of the last trade time of these currencies.

The default time that Microsoft provides is UTC.  The offset is set to -7 hrs, to convert to pacific time zone.

You can modify this if you need the time in a different time zone.

Visit https://savvytime.com/converter/utc to view offset

How to refresh data?

How to refresh Stocks data
How to refresh Stocks data

For more templates in this series, please visit indzara.com/stock-market-templates

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

Keltner Channels Technical Indicator

Posted on

In this article, we will be discussing the Keltner Channels technical indicator that is used to analyze stock performance.

I will provide

  • A brief introduction to the theory or concept behind Keltner Channels indicator
  • A demo of the template and how you can use it to calculate Keltner Channels for any stock you are interested in.
Keltner Channels Technical Indicator in Excel

Concept / Theory

The Keltner Channels idea is based on a concept introduced by Chester Keltner, but the methodology has evolved over time. However, the indicator is still named after Keltner.

As the name indicates, this indicator is like a band, channel, envelope or a region.

It uses Exponential Moving Average as the middle line and envelopes are calculated using Average True Range.

Calculation

  • Upper Envelope = EMA + 2*ATR
  • Lower Envelope = EMA – 2*ATR

EMA is typically a 20 period EMA.

How Keltner Channels indicator is calculated

Trading Signals

A breakthrough above the Upper Envelope signifies overbought conditions. A breakthrough below the Lower Envelope signifies oversold conditions.

TradingView

The following is an example signal strategy

The following is an example signal strategy

  1. Buy when the Closing Price crosses from above the lower envelope to below the lower envelope
  2. Sell when the Closing Price crosses from below the upper envelope to above the lower envelope

Keltner Channels Excel Template

The template will help you create the Keltner Channels indicator automatically and calculate the trading signals instantly for any stock.

Keltner-channels-Indicator
Keltner-channels-Indicator

The blue line indicates the Closing price. The shaded area represents the envelope from the lower envelope line to upper envelope line.

Download

Video Demo

Requirements:

To pull live Stock data from the services you would need either Microsoft 365 Subscription Plans for PC and Web or Excel for the web

How to Use:

Type in a stock sticker symbol on the top left.

Input stock symbol

If Excel does not recognize your stock symbol, it will pop up this data selector box.

Select the Stock

You can select the stock you are interested in.

Period

The template can pull up to 5 years of price history by default. You can choose how much history to display on the chart easily using the slicer buttons.

Period-for-the-Stock-price-history
Period-for-the-Stock-price-history

The options are 5 Days, Month Till Date, 1 Month, 3 Months, 6 Months, Year Till Date, 1 Year, 5 Years.

Interval

The template allows price history at 3 different intervals.

Interval-of-Stock-price-history
Interval-of-Stock-price-history

Daily/Weekly/Monthly are the options.

Input Parameters

Input parameters
  • EMA Len: You can type in a length in periods. The commonly used is 20.
  • ATR Len: The length in periods used to calculate the ATR.  We have used the EMA to calculate ATR in this template.

The chart will update automatically based on all the above inputs.

Signals

The template shows two types of signals (Buy, Sell) on the chart.

Keltner Channels Indicator with Trading Signals

The following rule generates the signals.

  1. Buy when the Closing Price crosses from above the lower envelope to below the lower envelope
  2. Sell when the Closing Price crosses from below the upper envelope to above the lower envelope

In addition, if the latest day has a signal, it will be displayed below the current price.

Stock Price

Timezone

On the top right you can see the maximum of the last trade time of these currencies.

The default time that Microsoft provides is UTC.  The offset is set to -7 hrs, to convert to pacific time zone.

You can modify this if you need the time in a different time zone.

Visit https://savvytime.com/converter/utc to view offset.

How to refresh data?

How to refresh Stocks data
How to refresh Stocks data

Resources

Keltner Channels – TradingView

For more Excel Templates for Stock Market, visit indzara.com/stock-market-templates

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

On Balance Volume Technical Indicator

Posted on

In this article, we will be discussing the On Balance Volume (OBV) technical indicator that is used to analyze stock performance.  

I will provide

  • A brief introduction to the theory or concept behind On Balance Volume indicator
  • A demo of the template and how you can use it to calculate On Balance Volume for any stock you are interested in.
On Balance Volume Technical Indicator in Excel

Concept / Theory

The basic theory behind the On Balance Volume indicator is that volume precedes price. Buying and Selling pressure precede actual price changes, and this indicator measures the buying and selling pressure by using volume.

  • When volume on PRICE UP days is greater than volume on PRICE DOWN days, OBV increases.
  • When volume on PRICE DOWN days is greater than volume on PRICE UP days, OBV decreases.

TradingView

On Balance Volume (OBV) Calculation

  • If Current Closing Price > Previous Closing Price, then Prev OBV + Current Volume
  • If Current Closing Price < Previous Closing Price, then Prev OBV – Current Volume
  • If Current Closing Price = Previous Closing Price, then Prev OBV

Since it is a cumulative type indicator with current value depending on previous value, it makes a difference when you start calculating this indicator.

If you start calculating with price history data of 1 years, versus 5 years, you will have a different OBV value today.

How On Balance Volume indicator is calculated

Trading Signals

When OBV is up, buying pressure is up and when OBV is down, then selling pressure is up.

TradingView

On Balance Volume Excel Template

The template will help you create the OBV indicator automatically for any stock.

On-Balance-Volume-Indicator-in-Excel
On-Balance-Volume-Indicator-in-Excel

The blue line indicates the Closing price. The Orange line represents the On Balance Volume.

Download

Video Demo

Requirements:

To pull live Stock data from the services you would need either Microsoft 365 Subscription Plans for PC and Web or Excel for the web

How to Use:

Type in a stock sticker symbol on the top left.

Input stock symbol

If Excel does not recognize your stock symbol, it will pop up this data selector box.

Select the Stock

You can select the stock you are interested in.

Period

The template can pull up to 5 years of price history by default. You can choose how much history to display on the chart easily using the slicer buttons.

Period-for-the-Stock-price-history
Period-for-the-Stock-price-history

The options are 5 Days, Month Till Date, 1 Month, 3 Months, 6 Months, Year Till Date, 1 Year, 5 Years.

Interval

The template allows price history at 3 different intervals.

Interval-of-Stock-price-history
Interval-of-Stock-price-history

Daily/Weekly/Monthly are the options.

Input Parameters

Input parameters

There is no input parameter that will change the On Balance Volume calculations in the template.  However you can change the blue line displayed to represent something other than Closing price (which is default) if needed.

Source: Closing price is the one that is used to display as blue line in the chart. However, you can choose to display values from Open, High, Low, Close or Volume.

The chart will update automatically based on all the above inputs.

Timezone

On the top right you can see the maximum of the last trade time of these currencies.

The default time that Microsoft provides is UTC.  The offset is set to -7 hrs, to convert to pacific time zone.

You can modify this if you need the time in a different time zone.

Visit https://savvytime.com/converter/utc to view offset.

How to refresh data?

How to refresh Stocks data
How to refresh Stocks data

Resources

Williams %R – TradingView

For more Excel Templates for Stock Market, visit indzara.com/stock-market-templates

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

Stochastic Technical Indicator

Posted on

In this article, we will be discussing the Stochastic technical indicator that is used to analyze stock performance.  

I will provide

  • A brief introduction to the theory or concept behind Stochastic indicator
  • A demo of the template and how you can use it to calculate Stochastic for any stock you are interested in.
Stochastic Technical Indicator in Excel

Concept / Theory

The Stochastics indicator was created by George Lane. According to him, Stochastics measures the momentum of price.

“Stochastics measures the momentum of price. If you visualize a rocket going up in the air – before it can turn down, it must slow down. Momentum always changes direction before price”

Stochastics is made of two lines %K and %D.

  • %K measures the current closing price against the range of prices (highest high and lowest low) within a certain period.
  • %D is the Simple moving average of the %K line.

Stochastic Calculation

%K = SMA ( (Current Close – Lowest Low) / (Highest High – Lowest Low) x 100, Smooth K)

  • SMA is the Simple Moving Average
  • Smooth K is the length or the number of periods to use in calculating the Highest Highs SMA

Most used period is 14.

%D = SMA(%K, Period D)

Period D is the number of periods used for calculating the simple moving average. Default value is 3 periods.

How Stochastic indicator is calculated

Trading Signals

Overbought conditions are when the Stochastic Oscillator crosses the upper threshold.

Oversold conditions are when the Stochastic Oscillator crosses the lower threshold.

The default thresholds are 20 for oversold and 80 for overbought.

TradingView

The following is an example signal strategy

  1. Buy when the %D drops below the Minimum threshold (20 by default)
  2. Sell when the %D exceeds above the Maximum threshold (80 by default)

Stochastic Indicator Excel Template

The template will help you create the Stochastic indicator automatically and calculate the trading signals instantly for any stock.

Stochastic Technical-Indicator-in-Excel

The lighter blue dashed line indicates the Closing price. The Blue line indicates %K and the Orange line the %D. The shaded area represents the band from the minimum threshold (20) to the maximum threshold (80).

Download

Video Demo

Requirements:

To pull live Stock data from the services you would need either Microsoft 365 Subscription Plans for PC and Web or Excel for the web

How to Use:

Type in a stock sticker symbol on the top left.

Input stock symbol

If Excel does not recognize your stock symbol, it will pop up this data selector box.

Select the Stock

You can select the stock you are interested in.

Period

The template can pull up to 5 years of price history by default. You can choose how much history to display on the chart easily using the slicer buttons.

Period-for-the-Stock-price-history
Period-for-the-Stock-price-history

The options are 5 Days, Month Till Date, 1 Month, 3 Months, 6 Months, Year Till Date, 1 Year, 5 Years.

Interval

The template allows price history at 3 different intervals.

Interval-of-Stock-price-history
Interval-of-Stock-price-history

Daily/Weekly/Monthly are the options.

Input Parameters

RSI Input parameters
  • Source: Closing price is the one that is used to display as light dashed blue line in the chart. However, you can choose to display values from Open, High, Low, Close or Volume. This does not change the calculation results for Stochastic. It’s purely to control what the light dashed blue line represents on the chart.
  • K: You can type in a length in periods. The commonly used is 14.
  • D: Commonly used is 3 periods.
  • Smooth: Commonly used is 3 periods
Minimum and Maximum thresholds
  • Min: This is the minimum %D threshold to generate signals. The commonly used is 20.
  • Max: This is the maximum %D threshold to generate signals. The commonly used is 80.

The chart will update automatically based on all the above inputs.

Signals

The template shows two types of signals (Buy, Sell) on the chart.

Stochastic Indicator with Trading Signals

The following rule generates the signals.

  • Buy when the %D drops below the Minimum threshold (20 by default)
  • Sell when the %D exceeds above the Maximum threshold (80 by default)

In addition, if the latest day has a signal, it will be displayed below the current price.

Stochastic Technical Indicator – Stock Price

Timezone

On the top right you can see the maximum of the last trade time of these currencies.

The default time that Microsoft provides is UTC.  The offset is set to -7 hrs, to convert to pacific time zone.

You can modify this if you need the time in a different time zone.

Visit https://savvytime.com/converter/utc to view offset.

How to refresh data?

How to refresh Stocks data
How to refresh Stocks data

Resources

Stochastic – TradingView

For more Excel Templates for Stock Market, visit indzara.com/stock-market-templates

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

Williams %R Technical Indicator

Posted on

In this article, we will be discussing the Williams %R (or %R) technical indicator that is used to analyze stock performance.  

I will provide

  • A brief introduction to the theory or concept behind Williams %R indicator
  • A demo of the template and how you can use it to calculate Williams %R for any stock you are interested in.
RSI Technical Indicator in Excel

Concept / Theory

The Williams %R indicator was created by Larry Williams who has created several technical indicators and is a famous trader.

Williams %R is a momentum-based oscillator to identify overbought and oversold conditions.

%R Oscillates between 0 and -100. When the value is closer to zero, it indicates overbought conditions and when closer to -100 indicates oversold conditions.

Williams %R Calculation

%R = (Highest High – Current Close) / (Highest High – Lowest Low) x -100

  • Highest High = Highest High for the look-back period.
  • Lowest Low = Lowest Low for the look-back period.

Most commonly used period is 14.

How Williams %R indicator is calculated

Williams %R is represented as a line on the chart and its values range from 0 to -100.

Trading Signals

Williams himself set the parameters of overbought as any reading between 0 and -20. His oversold parameters were readings between -80 and -100. It is important to note that these values are traditional examples. It may be beneficial for the technical analyst to set different parameters based on the specific instrument being analyzed. Wider or narrower overbought and oversold levels may be appropriate based on historical analysis.

TradingView

The following is an example signal strategy

  1. Buy when the %R drops below the Minimum threshold (-80 by default)
  2. Sell when the %R exceeds above the Maximum threshold (-20 by default)

Williams %R Excel Template

The template will help you create the Williams %R indicator automatically and calculate the trading signals instantly for any stock.

Williams-R-Technical-Indicator-in-Excel
Williams-R-Technical-Indicator-in-Excel

The blue line indicates the Closing price and the Orange line the Williams %R. The shaded area represents the Williams %R band from the minimum threshold to the maximum threshold.

Download

Video Demo

Requirements:

To pull live Stock data from the services you would need either Microsoft 365 Subscription Plans for PC and Web or Excel for the web

How to Use:

Type in a stock sticker symbol on the top left.

Williams % R Technical Indicator Input stock symbol

If Excel does not recognize your stock symbol, it will pop up this data selector box.

Select the Stock

You can select the stock you are interested in.

Period

The template can pull up to 5 years of price history by default. You can choose how much history to display on the chart easily using the slicer buttons.

Period-for-the-Stock-price-history
Period-for-the-Stock-price-history

The options are 5 Days, Month Till Date, 1 Month, 3 Months, 6 Months, Year Till Date, 1 Year, 5 Years.

Interval

The template allows price history at 3 different intervals.

Interval-of-Stock-price-history
Interval-of-Stock-price-history

Daily/Weekly/Monthly are the options.

Input Parameters

RSI Input parameters
  • Source: Closing price is the one that is used for calculations typically. However, you can choose from Open, High, Low, Close or Volume.
  • Length: You can type in a length in periods. The commonly used is 14.
  • Min: This is the minimum %R threshold to generate signals. The commonly used is -80.
  • Max: This is the maximum %R threshold to generate signals. The commonly used is -20

The chart will update automatically based on all the above inputs.

Signals

The template shows two types of signals (Buy, Sell) on the chart.

RSI Indicator with Trading Signals

The following rule generates the signals.

  • Buy when the %R drops below the Minimum threshold (-80 by default)
  • Sell when the %R exceeds above the Maximum threshold (-20 by default)

In addition, if the latest day has a signal, it will be displayed below the current price.

RSI

Timezone

On the top right you can see the maximum of the last trade time of these currencies.

The default time that Microsoft provides is UTC.  The offset is set to -7 hrs, to convert to pacific time zone.

You can modify this if you need the time in a different time zone.

Visit https://savvytime.com/converter/utc to view offset.

How to refresh data?

How to refresh Stocks data
How to refresh Stocks data

Resources

Williams %R – TradingView

For more Excel Templates for Stock Market, visit indzara.com/stock-market-templates

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.

Average True Range (ATR)

Posted on

In this article, we will be discussing the ATR (Average True Range) technical indicator that is used to analyze stock performance.  

I will provide

  • A brief introduction to the theory or concept behind ATR indicator
  • A demo of the template and how you can use it to calculate ATR for any stock you are interested in.
Average True Range (ATR) Technical Indicator in Excel

Concept / Theory

The Average True Range (ATR) is used to measure volatility. ATR is not used to indicate the direction of price.

ATR Calculation

ATR = Average of True Range

True range = max [ (high – low), ABS(high – previous close), ABS (low – previous close)]

How ATR (Average True Range) indicator is calculated

ATR Excel Template

The template will help you create the ATR indicator automatically for any stock.

Average-True-Range-ATR-Technical-Indicator
Average-True-Range-ATR-Technical-Indicator

The blue line indicates the Closing price and the Orange line the ATR

Download

Video Demo

Requirements:

To pull live Stock data from the services you would need either Microsoft 365 Subscription Plans for PC and Web or Excel for the web

How to Use:

Type in a stock sticker symbol on the top left.

ATR Technical Indicator Input stock symbol

If Excel does not recognize your stock symbol, it will pop up this data selector box.

Select the Stock

You can select the stock you are interested in.

Period

The template can pull up to 5 years of price history by default. You can choose how much history to display on the chart easily using the slicer buttons.

Period-for-the-Stock-price-history
Period-for-the-Stock-price-history

The options are 5 Days, Month Till Date, 1 Month, 3 Months, 6 Months, Year Till Date, 1 Year, 5 Years.

Interval

The template allows price history at 3 different intervals.

Interval-of-Stock-price-history
Interval-of-Stock-price-history

Daily/Weekly/Monthly are the options.

Input Parameters

ATR Input parameters
  • Source: Closing price is the one that is used for calculations typically. However, you can choose from Open, High, Low, Close or Volume.
  • SMA Length: You can type in a length in periods. The commonly used is 14.

The chart will update automatically based on all the above inputs.

Timezone

On the top right you can see the maximum of the last trade time of these currencies.

The default time that Microsoft provides is UTC.  The offset is set to -7 hrs, to convert to pacific time zone.

You can modify this if you need the time in a different time zone.

Visit https://savvytime.com/converter/utc to view offset.

How to refresh data?

How to refresh Stocks data
How to refresh Stocks data

Resources

ATR – TradingView

For more Excel Templates for Stock Market, visit indzara.com/stock-market-templates

Feedback

Please share your feedback on this template in the Comments below. If you find this useful, please share with your friends who may benefit from this content.