Intraday Equity Logbook for Zerodha Traders

Here is a simple logbook for equity trading for  Zerodha traders.

https://docs.google.com/spreadsheets/d/1nvdPRPa_nLb6givFuD057myKn_YcT7lOOrs2VvP-CiE/edit?usp=sharing

Get Real Time Data from Google Finance for NSE

A small program that I have written in Python 3.5  to retrieve data from Google Finance. All set and ready for running it on a  list of symbols.

Uses requests to retrieve the data from google finance and write to pandas. 

This is the structure of a typical URL for symbol TCS for a 5 minute data:

http://www.google.com/finance/getprices?i=300&p=2d&f=d,o,h,l,c,v&df=cpct&x=NSE&q=TCS

Program:

https://github.com/pavanmullapudy/get_real_time_google_finance_data

Screener to Shortlist Stocks based on Fundamental Analysis

So lets us look at some basic rules/criteria on the basis of which we can filter out stocks. In this method, we will not look at stocks from the point of view of a Chartered Accountant, we will instead look at it from a traders perspective.

The purpose is not to select the best stocks as it is to eliminate/reject stocks that are not suited for trading. From a given list of say 100 stocks, we can eliminate stocks that are not suited for trading.

Criteria 1 : P/B Ratio (Price to Book Value Ratio ) 

P/B Ratio	State of Stock
>5	Expensive	Reject Stock
2-5	Normal
<2	Undervalued

The P/B ratio is in many cases could vary based upon the industry. So some companies that own machinery and land are bound to have higher book value compared to a company in the services sector.

But for the purpose of our screening, it is better to avoid all stocks where the P/B Ratio is greater than 5.

 Criteria 2 : Debt/Equity Ratio

Companies with higher debt are more risky compared to companies with lower debt. For our consideration, we will  reject all stocks with Debt to Equity ration greater than 2

Debt /Equity Ratio	State of Stock
< 1	Good
1-2	Ok
>2	Problematic	Reject Stock

Criteria 3 : Interest Coverage Ratio

The interest coverage ratio measures how many times a company can cover its current interest payment with its available earnings. In other words, it measures the margin of safety a company has for paying interest on its debt during a given period. The lower a company’s interest coverage ratio is, the more its debt expenses burden the company. When a company's interest coverage ratio is 2.0 or lower, its ability to meet interest expenses may be questionable.

Any company with an interest coverage ratio of less than 2.0 will be automatically rejected.

Interest Coverage Ratio	State of Stock
< 2.0	Problematic	Reject Stock

Criteria 4 : Operating Profit Margin

If the (Sales - Expenses) are increasing over the last 5 years, the company is in good shape. The OPM % over last 5 years has to be positive (at the least). We will reject all stocks that have a OPM % in the negative.

Criteria 5 : Calculate Fair Value of Stock

Finally we need to calculate the price at which to purchase a given stock. For that we need to identify the fair value of the stock.

We calculate the EPS yearly growth taken over the last three years. 

Fair value Multiplier = EPS yearly growth / PE

One way of thinking about the PE ratio is that it is the "Expected Growth by the Market". As an example, if the P/E of Infy is 22.20 , the market expects a growth of 22.20 % per annum.

Example: If yearly EPS growth of Infy is 30 and PE ratio is 22.20, then Fair Value Multiplier =  EPS / PE = 30/22.20 =  1.35

Fair Value = (Market Price * Fair Value Multiplier)

If Fair Value of the stock is above the Current Market Price, then the stock is undervalued. 

Criteria 6 : PEG Ratio

The price/earnings to growth ratio (PEG ratio) is a stock's price-to-earnings (P/E) ratio divided by the growth rate of its earnings for a specified time period.

PEG Ratio = Price/EPS  / EPS Growth

Debt /Equity Ratio	State of Stock
< 1	Undervalued
1-2	Normal
>2	Expensive	Reject Stock

The lesser the PEG ratio, the better. For the purpose of our screner, we will reject all stocks that have a PEG ratio > 2.

All the data for calculations have been taken from Screener Here is the link to the actual Google Sheet:

https://docs.google.com/spreadsheets/d/1iYzfQ6oQjmW_tqz_sW2ERURJZVDUmqKcsKvYguI4rkI/edit?usp=sharing

A simple Stock Screener using Google Sheets for Intra Day Trading

So here goes the link:

https://docs.google.com/spreadsheets/d/17lBm-OU1Yv9UZ7VT26eB2TqgDy2NMcrHVohK4CqmWA8/edit#gid=0

In case you want to make any modifications to this google sheet, maybe add some more scrip's or delete some scrips, you have the following options: 

Method 1:
Go to Row # 2. Click on the various columns and copy the formulae. Paste it on your google sheet. Apply this formulae across all the cells in that given column

Method 2:
Login with your Gmail account. Go to the Main Menu of the Google Sheet. Click on File->Make a copy. This will create a copy of the google sheet "Intraday FnO Underlying" in your Google Drive. Now you can edit this new google sheet directly from your own Gmail Account. You can also add/delete rows and make any modifications as you deem necessary.

The screenshot below will provide he necessary guidance: