In this video, I'm going to show you how to calculate confidence interval for
population proportion.
The data I'm using, is the data we downloaded from New York Stock Exchange
closing data, at the end of a day, in September of 2015.
Symbols have been changed to comply with copyright laws.
Let's say, that in this situation, I'm interested in knowing what proportion of
the stocks had a positive change at the end of the day.
So, what we have recorded from the New York Stock Exchange,
the value of the stock at the end of the day.
What was the net change that it had and whether it was a negative change or
a positive change.
So, it has gone up, if this percentage is positive and it's gone down,
if this value is negative.
If I'm interested in knowing the proportion of what has gone up or not,
first I have to classify each stock as a positive value or a negative value.
So, how am I going to do that?
I'm going to answer a question like, has it gone up?
And to do this, I'm going to use a logical function from Excel.
So I'm going to say, if this value that I see here is a positive value,
so it's greater than 0, then return a value of 1.
Otherwise, return a value of 0.
So if you return this, and if I've done it correctly,
the first start that I have classifies as a 0, because it did not go up.
So, 0 means false.
No, it didn't go up.
And let me just copy this down until we get to a positive value.
Here we have a stock that went up on that day.
So if I've done this correctly, this should return a value of 1,
and indeed it does.
For everything else in between,
because everything is negative, it returned a value of 0, here.
Because the value that sits in the cell that I'm checking, the 11 is
greater than 0, it returns a 1 and I get to know that's a stock that has gone up.
So, you can essentially copy these down all the way.
So, I put my cursor here, double click, and it populates the entire thing.
So again, we have all of the stocks at that a,
which is over 2100 stocks recorded here.
So first, I'm going to find out how many went up.
I'm going to say number that went up.
And that number is simply sum of everything here.
Remember they get a 0 if they didn't go up.
So if I add all the 1's,
I will find out what was total number that went up at the end of that day.
So, Ctrl+Shift+down.
Close the parentheses, return, scroll up to see the number.
So, 383 stocks on that day went up.
So, if I want to know what is the proportion, I have to take this number and
divided by the total number of stocks that were reported back at the end of the day.
So, in order for me to know how many starts are in this column, or
basically in this column, I need to find the total number, which is count.
Count is simply, I can ask Excel to count the number of values that it finds here.
So, if I put my cursor again here, Ctrl+Shift+down,
close the parenthesis, return, I will see that on that day I had
2192 stocks that were reported in this data set.
So, what was the proportion of ups?
It is simply, 383 divided by 2192.
So, those two cells divided.
So, roughly about 17 and a half percent of the stocks went up, the rest did not.
So, this is definitely going to be a rough day for the Stock Market.
So, now let's look at what happens if we do sample.
So, an idea behind sampling is that you have the sample.
So, let's say You have 127 stocks that you have in your 401(k) or
you have invested in or whatever.
So, you basically hold a sample of the entire stock market.
How did you fare and can you estimate, based on your data,
what was the number of ups and downs in the stock market as a whole?
So, we will repeat the same thing here, I want to know how many went up
here in my stock, in my portfolio, and I'm going to repeat it the same way.
If this value is a positive value,
it is greater than zero, return a value of 1, otherwise return a value of 0, and
I'm going to say Return, put my cursor here and repeat it.
So, the entire 127 stocks that I own also gets repeated as 0 and 1.