Hello everyone.
Welcome to the day to capstone finance project solutions lecturer in this lecture.
We're going to go over the solutions for the finance project.
Let's go ahead and jump to Jupiter notebook to get started.
OK so here we are at the Jupiter notebook when we go out and scroll down to the get the data section
.
So the first thing we had to do is actually use pandas directly read from Google Finance and there's
a couple of notes here you had to install Pandurs data reader in order for this to work.
And like I mentioned in the previous lecture you can just do this by going to your command line and
saying Pipp install Pandurs dashed data reader or Konda install panda stashed data reader.
And again there's notes here or links or more installation guidance.
Ok then we wanted to do these imports right here.
I went ahead and just ran that since I was already filled out.
And now we're at the data section.
First thing I want to do is get data using Panas data reader and we can get stock information for the
following banks.
Bank of America Citigroup Goldman Sachs JP Morgan Chase Morgan Stanley and Wells Fargo says we're going
to be looking at banks and how their stock value change from and throughout the financial crisis and
what we want to do is figure out how to get stock data from January 1st 2006 all the way up to January
1st 2016 for each of these banks and we want to set each bank to be a separate data frame.
If the variable name for that bank being its ticker symbol and going to a few steps first we need to
use that time to set the start and end date time objects then we need to figure out the ticker symbol
for each bank.
Number three we need to figure out how to use data reader to grab info or information on the stock.
And there's a link here use this documentation page for hints and instructions.
And in fact that link is actually quite informative.
It essentially gives you the answer if you scroll down here a little bit on how to grab information
.
So if you scroll down we'll give you Google Finance how to grab information in stock prices from Google
Finance.
You can go ahead and essentially just copy and paste this code from the documentation and it will run
as expected.
So I'm going to go ahead and essentially copy and paste this code actually type it out so I can explain
it a bit.
We're going to go to start off with the start and end dates and then show you how to use the web data
reader.
OK.
First thing I want to do is say start and then call Date Time Date Time just a little strange to call
date time date time but that's just the convention.
And then we want to pass in the year the month and the day that's going to be our start date.
And again this is essentially just copying and pasting the code right here from the documentation.
And then I'm going to do the same thing but instead of star it's going to be my end date.
And this is going to be in sort of 2006 2016.
And then what we had to do is figure out the tickers for the various banks such as Bank of America and
the first one is actually kind of shown here the A C equals data that data reader BAMC.
Google start ends let me go and to show you an example of what that actually looks like by say B A C
equals data thoughts data reader assin B A C is a string Google and then the starts and ends.
All right.
Looks like it ran.
Let's go ahead and check up the result.
B A C.
And here we have B C which is essentially a data frame with a date index and then an opening price a
high price the low price the closing price and then the volume.
And this essentially just translates to the prices of the stocks of the opening price of the stock the
states the high and low values on that day.
And then the closing price on that date and then volume is just the actual volume how many stocks are
traded on that day.
Then what you essentially have to do is just repeat this for the other various banks and in order to
figure out the stock tickers for the other banks you can just use a simple google search you just Google
search the bank name such as Citigroup Plus stock ticker and you'd find it.
All right.
I'm going to go out and copy and paste the rest of these commands because it's essentially just exactly
what we did before.
So I have Citigroup is just see Goldman Sachs is just G-S J.P. Morgan Chase JPM Morgan Stanley's EMIs
and Wells Fargo WFC.
And the only arguments you pass the data reader are the stock ticker has a string and he repassing in
Google and that just means use Google Finance in order to grab the data.
You can also use Yahoo Finance if you want but I prefer Google Finance just because Yahoo recently had
a transition or was acquired so it's better to maintain it with Google and then you can also just put
in the start and end dates as Date Time information.
Let's go ahead and run this cell and see what happens.
That's so when you run it all together it may take a while because it's grabbing all this information
from the Internet.
So go ahead and just be patient while it runs.
There you go it's done.
Now let's go ahead and see in other data frame.
We go ahead and check out a mess for Morgan Stanley.
We can see it's the same thing that dates open high low close and then the volume traded names go all
the way down it goes from 2006 all the way to December 1st 2015.
So New Year's Eve now it's good and continue to the next task when a hash tag that out.
Now we wanted to create a list of ticker symbols as strings in alphabetical order call this list ticker's
.
Let's go ahead and do that we'll call ticker's and then passen BSEE.
Next one of the first quarter C then we have G-S then JPM and EMIs.
And then finally WFC and then we want to use PD dot com can you concatenate the bank data frames together
to a single data frame called Bank underscore stocks and you want to set the keys argument equal to
the ticker's list.
Also pay attention to access you concatenate.
And that's just a kind of a warning there that you had to set the access equal to once you want to concatenate
along the columns.
Let's go ahead and do that.
So I want to create the bank underscore stocks data frame and then I'll say PD concat and going to hacen
a list of all the data frames I want to concatenate.
So I want concatenate the BBC data frame the see G-S CPM amice Morgan Stanley and then Wells Fargo.
And the reason I'm not passing in these arguments is strings because these are the actual data frames
that it created up here.
All right so in passing in a list of the data France want to concatenate.
I want to concatenate them all along the columns which makes sense since they all share the same index
they all share the same certain and date time index.
I want to do this concatenation along the columns salsa axis equal to 1 and then he's argument is going
to be this ticker's list
and let's go ahead and see what bank stocks looks like now.
He's the head of it and we get something that looks like this.
Essentially now we have a multilevel index on the columns you have the bank stock ticker and then the
open high low close and volume and everything shares the same date time index which makes sense in that
we want to do is set the column name levels.
And this is actually filled out for you.
So in order to do that well we have to say banks underscore stocks.
Columns not names and then we set them to these levels the bank ticker and the stock info.
So once you run that sell and if you're at a Chick bank underscore stocks again the head of it it looks
like this.
And the only difference is now that this is actually labeled this first index level on the columns is
labeled bank think Bank ticker and then the second one is labeled stock info.
OK.
Moving along.
It's time to do some exploratory data analysis and before continuing what I wanted you to do is encourage
you to check out the documentation on not just multilevel indexing here but also using the dot X s
and we've actually gone over the excess the cross section method in the panel operations lecture.
So you can go ahead and review and go back to that lecture in case you want a video explanation of the
excess.
But basically this cross section method allows you to grab stuff inside a multilevel index and there's
some examples here.
I can go ahead and zoom in on them.
Hopefully come here and check out the examples you see this data frame you see that they can call a
cross section of a.
And you can also see that they can call cross-sections by passing in either a tuple or a list and grabbing
things on the inside.
All right.
You can go ahead and reference the panda operations lecture or my own explanation of that access but
it's kind of important they understand that because it's going to be a major part of this project as
we go along in order to grab information on specific banks or specific columns.
All right.
Our first exploratory data analysis questions to answer is What does the max clothes price for each
bank's stop throughout the time period.
There's two different ways we could do this.
One way is you could say something like this for tick in tickers.
Remember ticker's is that list of the ticker names say something like grab the bank stocks grab tick
and let's just see what that looks like if I say for instance me comment these lines out and just show
you what bank stocks look like as a reminder.
So we have head of bank stocks.
This is a multilevel index on the top multi-level columns.
If put in for instance b c then I get a data frame here which means I can call close off that data frame
which gives me this.
And since I want the max close price I can call max and it gives me something that looks like that which
means I would essentially just replace those commands.
We go back here to tick and then I'll actually I can just print this.
I can say something like Prince Tich comma that.
And here we have each bank stocks max close price throughout the entire time period.
That's one way to do this.
The better way to do this if you want to be kind of savvy with pandas is to use this access method and
what you do is you say bank stocks got access for the cross section you pass in the key you want to
do the cross section on in this case.
I want to do it on close.
I want to say it's on the columns axes since I'm doing it on the column so specify x 0 to 1 and then
also specify the level since it's inside and the level is called stock info.
Remember that's why we back above here.
Scroll back up.
We said stock info.
So this outer levels called Bank Ticker this level levels called stock info.
So we're basically asking.
Give me the closed column for all the banks and then we're going to go ahead and do just call that max
offer this and if we run this essentially get the exact same thing but it's a little nicer formatted
and it was just one line of clean code here.
All right.
So that was the max closing price for each bank's stock throughout the entire time period.
Now we're going to go ahead and do is create a new empty data frame called returns and this data frame
will contain the returns for each bank's stock and returns are typically defined by this equation where
you have a return for some time period and then pay the price.
But essentially what it is is it breaks down to the price at a time.
T in this case we're dealing with just dates.
So the price that a day divided by the previous day minus one.
So since you just the percent return and we can go out and say returns we'll make an empty data frame
PD data frame and run that.
So we have an empty data frame called returns and the state of frame will contain the returns for each
bank stock.
And here's the equation where we're going to do is move along and say we can use Panas t underscore
change or percent change method on the close column to create a column representing the return value
.
So our assignment was to create a for loop that goes in for each bank stock ticker creates this return
column and sets it as a column in the returns data frame.
Now this is definitely a little tricky.
So let's go ahead and break it down.
We want to create a for loop that goes for each bank stock ticker.
So I want to save for a tick in ticker's And then what I want to do is create a returns column so say
returns and so that's my new data frame over here.
I will create a new column and the column name will say be Tich concatenated with return and it's going
to be equal to the bank stocks at that tick closing price.
And then we just call PCTs change and this was kind of tricky to figure out.
So let's go ahead and run this and now let's check out our returns data frame or at least the head of
it and here ever returns data frame and it makes sense that our very first value has none or no because
you can't get a percent return on the very first day because there's nothing in the past to compare
it to.
So that makes sense.
And you can see here you basically get your return value or we're going to go ahead and do now is create
a pair plot using Seaborn of the returns data frame.
And there's a couple of questions there.
What stock stands out to you and can you figure out why.
What I'm going to go ahead and do is say just SNS dot pair plots and call returns.
Now if I just call returns you'll notice something happens we'll get an error and the error free scroll
down you'll notice that it says Max must be larger than men in range parameter and that's basically
just a call back to the issue here where you have a null.
So what you're gonna have to do specify that you want everything starting at index at location 1.
And now when you run that you should be fine.
Now per plus those take a while for very large data sets to keep that in mind as you wait for the plot
.
But you see something that looks like this.
And the question was what stock stands out to you.
Well the kind of look at this you'll notice that all these stocks look relatively normal as far as scatter
plots.
Looks like there's some correlation.
But one of them is kind of this weird straight line bundle.
And you notice that it happens along the sea return for Citigroup and Citigroup actually had a huge
stock crash.
And if you go to the Wikipedia link if you just look up Citigroup and come down here you'll notice that
November 2008 and this actually to do with part of the global financial crisis that by November 2008
Citigroup was insolvent and we'll actually see that huge crash when we actually visualize there Citigroup's
stock value throughout time.
But she can go ahead and kind of read about this but this kind of has to do more with the story behind
our data analysis on why this happened.
And this is available at the Wikipedia page for just Citigroup as a company.
All right.
So hopefully you noticed that kind of strange behavior of this weird return but that's actually do that
crash.
OK.
We're going to go ahead and stop it here.
And when we come back on part two we're going to investigate.
This returns day to frame a little more and then do some more investigation as far as the distribution
of the returns.
And then we'll visualize the data even more.
Thanks everyone and I'll see you at the next lecture