Automated Stock Analyzer


(Pattabiraman M) #42

HI, I was trying to implement this. I have trouble estimating cost of debt. Any pointers would help. Thanks.


(eyesice) #43

@pattu
Thanks for the awesome analyzer! Is the FCF coming directly from Morning Star? I think that the FCF is calculated by simply substracting Purchase of Fixed Assets from Operating Cash Flow. Shouldn’t it be

FCF = Operating Cash Flow - Purchase of Fixed Assets + Sale of Fixed Assets - Change in Capital Work in Progress?


(Pattabiraman M) #44

Thank you. Yes it is from Moring Star and they define it as
operating cash flow minus capital spending


(Pattabiraman M) #45

UPDATE!
Dear all,

The spreadsheet is now compatible with screener.in. User can download the excel file from screener and process it automatically.
Automated Stock Analysis with Screener.in data


(Growth_without Debt) #46

In recent launch of The Unusual Billionaires by Saurabh Mukherjea used 10 years data on YOY 10% growth in sales and YOY 15% growth in ROCE for compounding return companies.
Can screener have such filter? CAGR growth filter is there but would like to know filter to find company having YOY sales and ROCE growth consistently for 10 years.


(kiran) #47

Hello pattu,
Thanks for the great tool.
Was checking the previous version which uses data from morningstar. I was checking Godrej properties, and the free cash flow was 1676 mn. But the free cash flow per share was given as - 47.74
Do you know why that could be? Does the macro calculate it or is it just picked up from morning star?


(Pattabiraman M) #48

Thank you. No it just picks it up from morningstar.


(Pattabiraman M) #50

Sorry, I do not know if that is possible.


(Pattabiraman M) #51

Update: Moneycontrol Stock Price History Update for Stock Analysis Spreadsheets

Moneycontrol changed its site. So a modification was necessary to extract stock price data.


(Pattabiraman M) #53

UPDATE!
The 13th edition of the freefincal stock analyzer now auto-generates Hewitt Heiserman Jr.'s Earnings Power Box, thank to efforts of Rs. Srivatsan, who explained how to identify good business from bad one using it.
The automated stock analyzer pulls financials from morningstar, stock price history from moneycontrol and calculates intrinsic value in six different ways, along with Dupont analysis, Graham number, Piotroski and Altman Z-scores for financial health. You can get it from here

Please give it a try and let me know how it works. If you have an analysis method in mind, let me know.


(vinamra chaware) #54

Great work…Thank you for sharing…
1…But according to Hewitt Heiserman’s book
Defensive Earnings are calculated:-
PAT - Increase/Decrease in Fixed Asset(Capex) -Increase/Decrease in WC

2…If we use operating cash-flow instead of PAT we need not to adjust for change in WC as operating cash-flow itself is result of change in WC.


(vinamra chaware) #55

Please find attached template for screener Colgate-Palm.xlsx (235.6 KB)
Snapshot of Excel


(Pattabiraman M) #56

Many thanks. I see what you mean. So do you suggest that I just use FCF for defensive earnings?
I have another version based on screener templates. Will try and set up a similar box there too.


(vinamra chaware) #57

1…Yes FCF can be used as Defensive Earnings
But as Operating Cash-flow excludes taxes and interest accrued but not paid
Defensive earning as FCF won’t be comparable wrt PAT/EPS


2)Some companies include interest payment in net operating cash-flow while some won/t include


(Pattabiraman M) #58

I have updated the earnings power box from morningstar here. Thank you for your inputs @vinamrachaware


(Pattabiraman M) #59

Update: This is the earnings power with screener.in financials. Automated Earnings Power Stock Analysis With Screener.in data
Please let me know if there are any issues. Thanks.


(Sandeep Singh Rawat ) #60

First of all thanks for sharing the powerful excel sheet you have built and for knowledge sharing. I found it extremely helpful and it’s simply awesome

Hoping you don’t mind my suggestion, I have a small feedback on your website structure. Right now it’s very difficult to find the latest download link for the excel sheet. I was looking for the latest version of the sheet but had to go through lots of pages before I landed up on the right page. Unless it is intentional to make people go through the maze :wink: I would suggest you provide all direct download links to your tools on a single download page along with a link to their detailed learning page. That page can list all different version (1 to 13)

Btw it’s possible that it’s just me who found it difficult to navigate so no need to take it too seriously either :slight_smile:


(Pattabiraman M) #61

Thank you. I gain nothing by making it difficult for users to get to the latest version. You are right it is a problem for many. I just need to shake my laziness and restructure it. The one option that can work is, like you said have a single download page and refer to it, each I post a new calculator. Appreciate your feedback.


(Trilok) #62

Hi,
I am looking for nseindia link(like Bhavcopy) for CSV where I can get information about free float market cap.
Ofcourse I can get free float info here(https://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=YESBANK&illiquid=0&smeFlag=0&itpFlag=0)

But I am looking for downloadable nseindia link for CSV or some other parsable format

Thanks in advance


(Abhay Amrite) #63

Getting error 438. Not able to use the excel sheet. Can you assist.