Movie Stats .. Bollywood

Hi, I have been recently thinking of an area to analyse and after watching some bad movies, I decided to give a go for bollywood movies. Can we crunch it through numbers/stats to fin out whether the movie will do good or bad ( irrespective of what the movie is about). This project is comprehensive and it will take time to collect data, so i will go slowly

I took the 2001-2010 decade and tried to analyse the move gross by months. We see that that the month of Dec did not hold a lot of promise. It was obviously pre-Aamir Khan era. From 2007 there has been a surge in the gross collected in the month of December. A jump of almost 150% to 100 crore rupees

In the above images we can see a lot of shifts

1) Increase in movie gross Revenue ( in crores )

2) Change in the monthly Gross Revenue ( in crores )

It can be easily seen that purely on the basis of Month, we will not be able to ascertain the earnings. E.g. In the era 2002 to 2009, there were few months that would steal away most of the revenues. In 2007-2008 alone Aug, Oct and Dec stole away 50% of the yearly gross. In 2013 the top 3 months have taken away 38% of the revenue. The figure was even less in the years 2012 and 2011. Movies have begun to spread out evenly and there are more opportunities for newcomers and new genres

On running a simple linear regression between the
a) Earnings of Dec : Dependant Variable
b) Earnings of Aug, Sep, Oct, Nov : Independant Variables

	Coefficients
Intercept	11.28481646
Aug	0.281777793
Sep	0.280503546
Oct	-0.094513125
Nov	-0.419597559

It is inversely related to the month of November ( highly inverse )

There are more factors at play
1) Director/Producer
2) Actor
3) Festival
4) etc.. etc

I will be taking all of these into consideration in my next article. Please let me know if you have some other suggestions/ideas

Pulling Company Information through R

For some time now, I was grappling  with getting company fundamental ratios to be able to do analysis. I have written a small R script that downloads the necessary market data for that stock ( in the example AAPL ). The source of the data is YAHOO Finance

After execution the R workspace will contain 2 data frames
ratios    -> Quantitative Data
ratios2  -> Qualitative Data

Download File


I am working on getting more balance sheet related information. It is always better to have more information while doing analysis

If anybody wants to have the complete list of companies enlisted in NSE, you can get it here Download File

After searching around for some time, I wrote a script that will scrape and list the tickers etc for products around the world. The current list contains of 110,000 products. You can download the Excel file here Download File Timestamp : 2nd March,2015 ( I refrained from using csv, because the company name consisted of all sorts of characters which can meddle with string separation :) )

Cricket Stats

I was playing around with cricket data for some time now, and there are some interesting observations

1) Average Runs scored per match YOY

2) Average Runs scored when tier2 teams won the toss and elected to bat


  3) Average Runs scored when tier2 ( Kenya, Zimbabwe) teams won the toss and elected to bowl



The above stats would state that maybe 2014 was a good year for cricket, but we have forgotten one important measure. The number of matches. More the number of matches, more the normalization ( averaging out )

In the following graphs, I plotted the average runs and the number of matches YOY for all the 3 forms of cricket

1) ODI

 2) T20

3) TEST

We see that for the year 2014 there is a surge in the number of runs scored in all 3 versions of the game. However, the data has fewer matches so statistically we cannot say for sure that 2014 has been a very good year.
However, I always thought that through the years, the number of runs scored have always been increasing, but that is not what we observer, especially between 2009 and 2013. Let us dig deeper

We see that in the year 2014, there were a lot of matches > 30 with the total being above 500. This drove the average up. From the graph we can make some more observations.

Now, we will  be applying similar stats on the following factors
1) Wickets fallen in a match
2) Runs scored till fall of first wicket
3) % of highest wicket partnership vs total runs

Will come up with the details

Potholes at Traffic Signals

Everybody must have at some time or the other faced a situation where we are stuck at a traffic signal, idly looking at the smoke coming out of the nearby truck. We keep looking ahead to notice the reason the traffic is held up. When we reach the signal, we find it was a pothole. How could the pothole hold up the entire traffic? It is very well true

Assumpions

average length of the vehicle	2.5
average speed of vehicles ( km / hr )	30
time for whch the signal is open ( seconds )	60
time for whch the signal is closed ( seconds )	180
idling losses ( average ) mL/hr	500
Road Length ( in m )	500

The above chart calculates the loss in  litres of petrol, per crossing, per period ( signal open + signal close ) assuming that there are 3 lanes. The x axis is the average speed of  vehicles, and the y axis shows the loss of oil due to "Idling"

The following link will now give you vehicle population of India. A graphical representation of the same is as follows

We see that there has been a humongous increase in 2 wheeler and small 4 wheelers in the last 2 decades. Assuming that out of this 50% vehicles get stuck at traffic signals (which is highly optimistic) the loss of oil runs into millions per year

With the following assumptions

Idling losses ( ml/Hr  )	200
Average time wasted at traffic/pothole ( hr/day )	0.5

The total cost currently would stand not less than 100 million INR. It is obviously much more but I tried to arrive at a calculated value with a conservative approach

Crime Against Women

Crime against women is on the rise, but what we read in the newspapers about violent attacks in only a tip of the iceberg. People might think molestation/rape is the foremost crime, but it is not. According to the data pulled from data.gov.in

1) DOWRY is one of the foremost reasons of crime against women and Andhra Pradesh has a huge chunk of it.  The numbers include cases registered for dowry prevention act

2) The following graph sums up all the registered cases by State. Uttar Pradesh might be sharing a low % here but we can attribute it to the low level of awareness of filing a case. I am trying to incoporate these factors but will have to get more data

3) The following graph looks at the age group of the people who commit crimes. As expected most of them lie in the 18-30 range

4) On trying to find out whether rural/urban has a part to play, it was found that the rural graph has more correlation than the urban graph

5)  I regressed the crime occurrences with the following factors
POPULATION  
GROWTH  
RURAL  
URBAN  
AREA 
DENSITY  
SEXRATIO

 The following are the results of the regression. We can see that the R Square is too less to say anything definite about the relationship of crime and the geographical boundaries. There are other factors

ALL INDIA
Age Group	R- Squared
<18 td="">	5.82%
18 to 30	16.76%
30 to 45	14.86%
45 to 60	13.97%
> 60	11.77%

NORTH STATES
Age Group	R- Squared
<18 td="">	4.55%
18 to 30	7.38%
30 to 45	5.95%
45 to 60	5.70%
> 60	3.34%

FUNDAMENTAL RATIOS

Fundamental Ratios play a very important part in finding an equity investment of your risk appetite. I collected the fundamental ratios data for all the listed companies on NSE ( and BSE )  and tried to listout few stocks that should be considered for further analysis

Criteria 1 : P/E Ratio < 10 , SALES > 800 crores

Query1
company	pe	eps	Sales ( in crores )
National Wind Power Corporation Ltd.	3.27	-0.01	975.34
Nagarjuna Fertilisers & Chemicals Ltd.(Old)	6.88	2.27	818.08
Trident Ltd.	5.14	6.33	913.26
National Steel and Agro Industries Ltd.	3.27	6.84	975.34
Monnet Ispat & Energy Ltd.	8.4	10.12	893.74
Sintex Industries Ltd.	8.92	10.77	998.14
Prakash Industries Ltd.	4.91	12.88	811.01
Uflex Ltd.	9.16	17.91	825.84
Diamond Power Infrastructure Ltd.	6.17	20.12	805.04
Escorts Ltd.	7.48	20.53	992.63
Kothari Products Ltd.	3.88	63.79	934.64
Mahanagar Telephone Nigam Ltd.	0.25	124.21	856.02

We see that MTNL has very strong fundamentals. IF we look at the stock price over the last 10 years

Click here for Image

This stock was unperturbed by even the 2008 Financial crisis. Such a stock is indeed very handy to have in your portfolio. Besides it also gave a 10% dividend

Another stock to look out for is Escorts Limited. This is a company based on agricultural machinery. With agriculture certain to get a big push from the government, it will be a good buy. One thing to note from its graph ( in fact all the graphs of its peers ) is that these stocks did not get affected much in the 2008 crisis. However, post 2008, people started putting in a lot of money on these stocks, leading  to a bubble. These stocks are a very good buy at times of a crisis

Click here for Escorts Chart

Click here for Jain Irrigation Chart 

Now if we go to the other side of the spectrum, we see companies with good P/E ratios but very low EPS too. These are extremely stable stocks with little volatility over a large period of time. A take a look at “National Steel and Agro Industries” reveals that the stock price graph over the last 10 years will reveal that it has stayed put at the 20 price mark. However the stable performance translates to huge dividends . National Steel and Agro gave a dividend of 150%

Criteria 2 : P/E Ratio > 700

Query1
company	P/E	EPS	SALES	Dividend %
Jain Irrigation Systems Ltd.	994.16	0.08	1	25
Futuristic Offshore Services & Chemical Ltd.	930.11	-0.13	2	30
Future Retail Ltd.	930.11	0.12	2	30
Visagar Polytex Ltd.	917.31	0.6	18.59	10
20 Microns Ltd.	793.61	0.04	81.32	10

A very high P/E will immediately tell you about a company that is new or expanding its business in another domain. This will often have lower dividends

• Visagar went on NSE on Jun 2013
• 20 Microns in 2009

(All the data has been taken from money.rediff.com and google finance )