Automated Stock Analyzer


(Pattabiraman M) #1

Dear all, I have an academic interest in stock analysis and valuation. I have made an excel sheet based analyzer which pulls financials from morning star, value research and price history from money control and has 5 valuation methods.
I was pleasantly surprised to see someone refer to it, in this forum. Mine being a mf-centric blog, the sheet has not been evaluated by a community of stock investors interested in analysis and valuation
If It does not break any rule of this forum, I would appreciate if members can provide feedback on the analyzer. How can I make it better, what can be added etc.

NB: I do not invest in direct equity and hence would require ELI5 like simple explanations.
Also I do not intend to profit in any way from this sheet or your comments except to learn from them.

The latest version of the analyzer can be found [here][1]

Thanks in advance.
[1]: http://freefincal.com/update-automated-stock-analyzer-v-6-1-workaround-for-moneycontrol-stock-price-issue/


A tool to calculate annualized returns (IRR) from Stock investments
(Tarang Mehta) #2

@pattu Thank you for sharing your sheet its wonderful. your skill with excel is commendable and it would be great if you could briefly explain how to automatically get the data downloaded from these sites as it happens in your sheet.


(Pattabiraman M) #3

Thank you. Data downloading from Excel is trivial. You only need the url to based on a formula. That is a code for each stock and rest of the url elements should be constant. In such a case, all you need is a database of stock codes

Sometimes you can get it from the sitemap and sometimes you will have to use a link extractor on a listing of stocks, get the urls, extract the code from the urls with word of excel (simpler than I make it sound) and then tabulate them and use it like I did.
If you need help downloading stuff, let me know and I could help if possible,


(Tarang Mehta) #4

Thank you pattu for the swift reply. It is something that i have been wanting to learn. My lack of knowledge on the subject is not helping me to understand how to go about it. do you know any links to a video or an article which would explain it in a more detailed fashion. Thanks in advance :smile:


(Pattabiraman M) #5

I learnt by recording excel web queries It will be stored as VBA code which I then modify bit and bit and learn.


(Vikas) #6

Hi Pattu,
I have downloaded your sheet. Thanks a lot for this. I was trying to make some modifications but unable to grasp you excel code. It would be helpful if you have some video sessions showing how you pulled the data from webpages.


(Pattabiraman M) #7

There are different kinds of web queries. Suggest you go to VR online and go to a stock page.

Use that URL and record a macro for data query in excel for the financials table.
Now you will have the VBA written for you by Excel.

Compare that code with mine to see how I have modified it. It is the simplest way to learn. If you have particular site in mind, you send me an email and I can help you pull data from it.


(Vimal) #8

@pattu: Thank you for the great work. I also liked your blog post on frugality. I came across your tool last week through AlphaIdeas, which is another great source of interesting articles.

For those looking for Excel data, take a look at QuantsPartner. It also has an Excel sheet for historical NSE prices (on the right sidebar on their webite).


(Pattabiraman M) #9

Thank you. Yes, am aware of quantspartner,


(Pratyush Mittal) #10

Hi Pattu, this looks pretty amazing.

We created Screener with a very similar approach.

I would love to talk to you over phone to understand more about it.


(Pattabiraman M) #11

Thank you. Sent you a message.


(Balaji Sridharan) #12

@pattu Nice work!

Thanks

Balaji


(Manoj) #13

Would like to understand the formula you came up with. Please share the screener link.


(Sachin) #14

@pattu Very good work :slight_smile:
Very handy tool to quick start analyzing company


(vivek bothra) #15

Sharing another tool which I have been using for last year or so, Happy for you to customize link

Looking for feedback from Seniors, The link above has some you tube videos on how you can use it

Value_Stock_Tankrich_v1.23.xlsx (96.9 KB)

Taking on @Donald 's advise when you throw something useful to a community of smart people it can really improve investing life of community at large !


(Pattabiraman M) #16

The latest version now computes Piotroski score for last 9 FY.
Automated Stock Analysis with the Piotroski Score


Excel Stock Screener: Please evaluate and help improve this tool
(Srinivasan Sundaram) #17

Pattabiraman

was trying the worksheet. got this error


(Srinivasan Sundaram) #18

It put was for " Kitex garments " analyser considers " Colgate "


(Pattabiraman M) #19

@Doonsrini, error corrected and file reuploaded. Thanks.


(Srinivasan Sundaram) #20

unable to pull value research data