Fractions

What is a fraction?

A “Fraction” means one piece of a whole. You can use fractions in any case where it might be useful to look at something in parts, rather than the whole thing at once.

The most delicious fractions are slices of pizza. If the pizza is in 8 slices, we know that there are 8 parts. This means any time we’re talking about its parts, we know it is “__ out of 8”, or “__ / 8”. For the whole pizza, we have all 8 slices.

Now let’s eat a slice! That will remove 1 of our slices and leave us with 7 and we had 8 slices originally. Or as a fraction:

The bottom number in the fraction is the whole and the top number in the fraction is how much we have currently (in this case, seven slices of pizza).

Example Using Your Portfolio

If you look at your pie chart, you can also see examples of fractions with your pie chart:

In this case we can’t divide the pie into equal pieces like we did with the pizza, since some stocks have a lot more than others. However, we can still show what “Fraction” of our portfolio is taken up by each stock.

In this example, lets say our portfolio has NKE(Nike), MCD (Mcdonald’s), NFLX (Netflix), AAPL (Apple).

Even though each stock is taking up different amounts of the whole, if we divide our portfolio in to equal parts, we can still get the fraction taken up by each stock.

Now if you want to know how much of each stock we have as a fraction. We count how many wedges (or slices) we have total: 10

Then we count how many of each stock we have (count the same colors)

• NKE: 1
• MCD: 2
• NFLX: 3
• AAPL: 4

Just like before, the bottom number in the fraction is called the “Denominator” and is how many parts are in the whole.

The top number in the fraction is called the “Numerator” is how many parts we have:

If we added all of of these fractions together we would get 10 / 10 which is equal to 1 (meaning the whole).

Comparing Fractions

You can only compare two fractions that have the same “Denominator“. For example, we know that 3/10 is bigger than 2/10, but you cannot directly compare it to 2/3. When you see the “/” sign, or whatever separates the numerator and the denominator, it means “Out Of” (so “1/10” means “1 out of 10”)

If you do want to compare two fractions, one way is to multiply them so their denominators are the same. In this example, we can convert our fractions to both be showing their value out of 30 parts instead of 10 or 3.

To do this, multiply both the numerator and denominator of each fraction until the denominator is the “Common” number.

As long as you multiply the numerator and denominator by the same number, the fraction’s value will stay the same (“2 out of 3” is the same as “20 out of 30”). Now both of our denominators are 30, so we can compare them directly!

When You Cannot Use Fractions

Fractions are only used to look at parts of one thing, they are not used to compare different things. For example, we can use a fraction to show how much of our portfolio is made up of one stock, but we cannot use a portfolio to compare a company’s stock price to how much money it makes.

Percentages

Fractions work well when what we are looking at is always different parts of the same whole, but when we want to compare parts of different things, we need to use percentages. A percentage is a calculation that will tell you how big one thing is in relation to another thing. For example, you can use a percentage to tell how big your current portfolio value is compared to how much you started with.

Percentages work like fractions, but the denominator is always 100, so you can always know which percentage is bigger or smaller. You can convert any fraction in to a percentage to compare them. This means when you see a percentage, the “%” sign means “Out of 100“.

Calculating Your Portfolio Return

In the rankings page you will be able to see Gain / Loss (%). We call this your Portfolio Return.

This is calculated by looking at the current value of your portfolio and compare it to your starting value and multiplying by 100.

Or ((Current Value / Starting Value) – 1) * 100 . You have to do it in this order: (Current Value / Starting Value) then subtract 1 and then multiply by 100.

1. Divide Current Value / Starting Value. This scales down both numbers so that “Starting Value” = 1. If your Current Value > Starting Value, the number you’ll get will be bigger than 1. If your Current Value < Starting Value, this number will be less than 1.
2. Subtract 1 from the result. This means makes the “comparison number” 0 instead of 1.
3. Multiply the result by 100. This makes your comparison 100 instead of 0.

For education1 on the rankings page above, you can calculate the Portfolio Return with the same steps:

1. 103,985.43 / 100,000 = 1.0398543
2. 1.0398543 – 1 = 0.0398543
3. 0.0398543 X 100 = 3.98543% (we around the percentage to 2 decimal places, so it appears as 3.99%).

Calculating Stock Return

Percent Return is very useful for comparing different stocks too.

The NFLX stock (1.54%) has a higher percentage return than the AAPL stock (1.24%) even though the number of AAPL (11.85) is higher than (8.05).

$11.85 is the amount of dollars you gained. The 1.24% is how much the stock price went up by. The percentage (bottom number) is much more important than the amount of dollars (top number), because it tells you how much the value changed compared to the price you bought it at.

This is because of the number of shares and the price. To calculate the percentage, compare the last price and divide it by the price paid or

((Last Price / Price Paid) – 1) * 100 :

AAPL :  ((96.70 / 95.52) -1) *100 = 1.24%

NFLX: ((106.06.70 / 104.45) -1) *100 = 1.54%

Relationship Between Percentages And Fractions

Lets go back to the pie charts we were using for fractions. Every fraction can be written as a percentage, with the “Whole” equalling 1

We bought 10 shares of AAPL (Apple), 5 shares of NKE (Nike) and 5 shares of NFLX (Netflix).

The numbers show the percentage of each stock’s value over your total value of your portfolio. Written as a fraction, it would be

Value of this stock in your portfolio / Total Value Of All Your Stocks

Going back to the portfolio example, we can calculate the percentage of each stock by comparing their Market Value with the total value of all of our stocks

To get these numbers we first grab the market value for each stock and add them together, so in our example:

292.75 (NKE) + 958.20 (AAPL) + 532.00 (NFLX) = 1782.95 = Total Value

Then if we want the percentage like in the pie chart for NKE, we compare the value of NKE to the Total Value:

NKE: ((292.75 / 1782.95) – 1 * 100 = 16.4 %

AAPL: ((958.20 / 1782.95) – 1 * 100 = 53.7 %

NFLX: ((532 / 1782.95) – 1 * 100 = 29.8 %

Both the fractions and the percentages will always equal 100 / 100 or 100% because our pie is whole.

By summing up 16.4 % + 53 .7 % + 29.8 % = 100 %

When To Use Percentages

Percentages are used normally to calculate the growth in something over time (like your portfolio return), or to compare parts of a whole when the denominators would be bigger than 10 (in our fraction conversion example, we can also say 2/3 = 66.6% and 2/10 = 20%).

Ratios

Ratios are a lot like fractions, but the biggest difference is that we only use ratios to compare different things. For example, if we want to compare the price of a company’s stock with how much money that company makes per share, we would use what is called the “Price – Earnings Ratio”. Since the “price of a stock” is not a part of “how much the company makes per share”, we would not be able to use a fraction. We would want to know the P/E ratio because that tells us how much the company is actually making compared to how much we’re paying for a share of it.

We can actually get all of this information on the Quotes page and find the P/E Ratio ourselves. EPS is the “Earnings per Share”. With Apple (AAPL) as the example:

The stock price is $100.15, while the Earnings-per-Share (EPS) for the last 12 months is $9.21. We can wright the ratio as

$100.15 : $9.21

When we read ratios, the “:” symbol means “to”, so we would say “100.15 to 9.21”.

Calculating this value is also easier than calculating a percentage. Simply divide the number on the left by the number on the right:

100.15 / 9.21 = 10.5 = PE Ratio

The P/E ratio gives an idea of how much investors are valuing the company’s current income – high P/E ratios mean investors expect the revenue to grow a lot in the future, low P/E ratios mean investors think the company will grow more slowly. By calculating the P/E ratio for different companies, you can compare the investor attitudes easily.

Pop Quiz

[qsm quiz=53]

In this article we will be looking at how you can use Excel to keep track of your account’s performance. This is meant as a basic guide for people who have little or no experience with Excel.

Using Excel To Track Your Stock Portfolio – Getting Some Data

Before we can do anything with Excel, we need to get some numbers! The information you use in excel is called “Data”. Some of it we will need to write down, some can be copied and pasted, and some we can download directly as an excel file.

Getting Your Historical Portfolio Values (Typing Numbers In The Spreadsheet)

You can find your Historical Portfolio Values on your Dashboard page, right above your portfolio value chart:

This will download a spreadsheet with your portfolio values, number of trades, and your rank for each day you were in your current contest.

Getting Historical Prices For Stocks (Copy And Pasting Data In To A Spreadsheet)

For this example, we want to get the historical prices for a stock so we can look at how the price has been moving over time. First, a new blank spreadsheet in Excel.

We will use Sprint stock (symbol: S). Go to the quotes page and search for S:

Next, click the “Historical” tab at the top right of the quote:

Next, change the “Start” and “End” dates to the time you want to look at. For this example, we will use the same dates that we saved for our portfolio values, January 11 through January 15, 2016.

Once you load the historical prices, highlight everything from “Date” to the last number under “Adj. Close” (it should look like this):

Now copy the data, select cell A1 in your blank excel spreadsheet, and paste.

Congratulations, we have now imported some data into excel! Notice that your column headings are already detected – this will be important later.

From there, there are few things we would like to change.

Changing The Order Of Your Data

First, this data is in the opposite order as our portfolio values. To get it in the same order, we want to sort this table by date, from oldest to newest. At the top menu, click on “Data“, then click “Sort“:

You can now choose what we want to sort by, and how to sort it. If you click the drop-down menu under “Sort By”, excel lists all the column headings it detects (select “Date“). Next, under “Order”, we want “Oldest to Newest“:

Now your data should be in the same order as your portfolio values from earlier.

Changing Column Width

Next, you’ll notice that “Volume” appears just as “########”. This is not because there is an error, the number is just too big to fit in the width of our cell. To fix this, we can increase and decrease the widths of our cells by dragging the boundaries between the rows and columns:

Tip: if you double click these borders, the cell to the left will automatically adjust its width to fit the data in it.

If you want to automatically adjust all your cells at once, at the top menu click “Format”, and “Auto Fit Column Width”:

Once you’ve adjusted your volume column, everything should be visible!

Removing Columns You Don’t Need

I think that we will only want to use the Adj. Close price in the calculations we will be doing later (the “Adj. Close” price is the closing price adjusted for any splits or dividends that happened since that day). This means I want to keep the “Date” and “Adj. Close” columns, but delete the rest.

If you try to just select the data and delete it, you’ll end up with a big empty space:

Instead, click on “B” and drag all the way to “H” to select the full columns:

Now right-click and click “Delete”, and the entire rows will disappear. Now the Adj. Close will be your new column B, with no more empty space. You now have your historical price data, so save this excel file so we can come back to it later.

Getting Your Transaction History And Open Positions

Like your historical portfolio value, we make this easy – next to your Open Positions and your Transaction History, you will find more Excel export buttons:

Regardless of the date range showing on the transaction history page, the export will pull your entire transaction history for this contest.

Using Excel To Track Your Stock Portfolio – Graphing

Now that we have some data, let’s make some graphs with it! We will go over how to make line graphs of your daily portfolio value and your portfolio percentage change, plus a bar chart showing your open positions. This is usually the most fun part of using excel to track your stock portfolio.

Line Graph – Your Daily Portfolio Value

First, we want to make a line graph showing our daily portfolio value. First, open your spreadsheet that has your daily portfolio values:

Next, highlight your data, and click “Insert” on the top tab:

Here, under the “Charts” section, click on the one with lines, and choose the first “2d Line Chart“:

And that is it! Your new chart is ready for display. You can even copy the chart and paste it in to Microsoft Word to make it part of a document, or paste it into an image editor to save it as an image.

Line Graph – Portfolio Percentage Changes

Next, we want to make a graph showing how much our portfolio has changed every day. To do this, first we need to actually calculate it.

Doing calculations in Excel

In the next column we will calculate our daily portfolio percentage change. First, in the next column, add the header “% Change”

Now we need to make our calculation. To calculate the percentage change each day, we want to take the difference between the most recent day’s value minus the day before, then divide that by the value of the day before:

Percentage Change = (Day 2’s Value – Day 1’s Value) / Day 1’s Value 

To do this, in cell C3 we can do some operations to make the calculation for percentage change. To enter a formula, start by typing “=”. You can use the same symbols you use when writing on paper to write your formulas, but instead of writing each number, you can just select the cells.

To calculate the percentage change we saw between day 1 and day 2, use the formula above in the C3 cell. It should look like this:

Now click on the bottom right corner of that cell and drag it to your last row with data, Excel will automatically copy the formula for each cell:

You now have your percentages! If you want them to display as percentages instead of whole numbers, click on “C” to select the entire column, then click the small percentage sign in the tools at the top of the page:

Making Your Graph With Only Certain Columns

Now we want to make a graph showing how our portfolio was changing each day, but if we try to do the same thing as before (selecting all the data and inserting a “Line Chart”, the graph doesn’t tell us very much:

This is because it is trying to show both the total portfolio value and the percentage change at the same time, but they are on a completely different scale!

To correct this, we need to change what data is showing. Right click on your graph and click “Select Data”:

This is how we decide what data is showing in the graph. Items on the left side will make our lines, items on the right will make up the items that appear on the X axis (in this case, our Dates).

Uncheck “Portfolio Value”, then click OK to update your graph:

This is closer, but now we want to move the dates back to the bottom of the graph (here they are along the “0” point of the Y axis).

To do this, right-click on the dates and select “Format Axis”:

A new menu will appear on the right side of the screen. Here, click “Labels”, then set the Label Position to “Low”.

Congratulations, your graph is now finished! You can now easily see which days your portfolio was doing great, and which days you made your losses.

Bar Chart – Seeing Your Open Positions

Next we would like to make a bar chart showing how much of our current open positions is in each stock, ETF, or Mutual Fund.

First, open your spreadsheet with your Open Positions. It should look something like this:

Since we want to make a bar chart, we can only have two columns of data. We want one column showing the symbol, and a second column showing how much it is worth. The “Total Cost” column is the current market value of these stocks, so that is the one we want to keep. However, we don’t want to delete the quantity and price, since we might want it later. Instead, select the columns you don’t want, and right-click their letter (A and C in this case). Then, select “Hide”:

Now the columns that we don’t want in our chart are hidden. We can always get them back later by going to “Format” -> “Visiblity” -> “Unhide Columns”. Now select your data and insert a “Bar Chart” instead of a “Line Chart”:

Before you’re finished, your chart will say “Total Cost”. You can change this by clicking on “Total Cost” and editing to say whatever you would like (like “Portfolio Allocation”):

This graph is now finished, but you can also try changing the Chart Type to try to get a Pie Chart. First, right click your graph and select “Change Chart Type”:

Next, find the “Pie” charts, and pick whichever chart you like the best.

Last, now we don’t know which piece of the pie represents which stock. To add this information, click your pie chart, then at the top of the page click “Design”. Then select any of the options to change how your pie chart looks.

Congratulations, you’ve converted your bar chart into a pie chart! This one should look almost the same as the one you have on the right side of your Open Positions page.

Using Excel To Track Your Stock Portfolio – Calculating The Profit And Loss Of Your Trades

The most important reason you would want to use excel to track your stock portfolio is trying to calculate your profit and loss from each trade. To do this, open the spreadsheet with your transaction history. It should look something like this:

Tip: If you have not bought and then sold a stock, you can’t calculate how much profit you’ve made on the trade.

First, we want to change how the data is sorted so we can group all the trades of the same symbol together. Use the “Sort” tool to sort first by “Ticker”, next by “Date” (oldest to newest).

For DWTI and SPY, we haven’t ever “closed” our positions (selling a stock you bought, or covering a stock you short), so we cannot calculate a profit or loss. For now, hide those rows.

Now we’re ready to calculate! Lets start with the trade for S. This one is easy because the shares I sold equal the shares I bought. This means if we just add the “Total Amount”, it will tell us the exact profit or loss we made on the trade.

This does not work for UWTI, because I sold a different number of shares than I bought. This means that I need to first calculate the total cost of the shares I sold, then I can use that to determine my profit.

First: multiply your purchase price times the number of shares you sold:

Second: add this number to the “Total Amount” from when you sold your shares.

Now you have your profit or loss for this trade. Note: this is the method for if you bought more shares than you sold – if you bought shares at different prices, then sell them later, you’ll need to calculate your Average Cost to use in your calculation.

Pop Quiz

[qsm quiz=52]