Google finance and spreadsheets tutorial part 1

Google finance is good source of stock data and Google spreadsheets is online tool that you can do similar calculation as in Microsoft Excel. Because tool is entirely online, you can fairly easily import data from websites into it. Link to final creation is lower part of this article if you are in a hurry.

Starting up and google finance integration

First you need to setup gmail account. Then browse to https://www.google.com/drive/

And there to New → Google Sheets

google_docs1 Google finance and spreadsheets tutorial part 1

If you have used Excel, you know how to use basic functionality. There is of course help section also in Google docs and Google Finance.

Let’s make example spreadsheet with stock ticker list:

google_docs3 Google finance and spreadsheets tutorial part 1

Let’s start for Visa row (row number 4)

To get data from Google finance use this format

=GoogleFinance(“Symbol”, “Attribute”)

So for our first parameter we have “Move today %”

to cell C4 :=GoogleFinance(B4,”changepct”)

B4 refers to ticker name for Visa (V)

Next we have “Number of shares owned”

Use this as following: If you have purchased stock in two or more occasions. For example for 100 in two 50 purchases : =50+50

This way have information for average share price calculation later

Next is “Average cost of shares”

Let’s say you paid 60 dollar for the first 50 shares of Visa and 70.5 dollars for the second lot.

Average price can be calculated by weighted average equation:

=(50*60+50*70.5)/100, but because we have total number of shares in previous cell, we just write =(50*60+50*70.5)/D4

Result is 65.25

Purchase cost

We have average price and number of share so just multiply them

=E4*D4

Result is 6525 dollars

Stock price now is next

=GoogleFinance(B4, “price”)

Friday close price at time of writing was $80.47

Lastly we have Return $

Current position value – purchase value which in this case is:

=(G4*D4F4)

Fill out the next two stocks same way it should look something like this(different share cost is ok):

google_docs_return Google finance and spreadsheets tutorial part 1

Next we can add total sums and some color to the system

Move total word more to right and write equation for total return and port folio value

First let’s add row for position value ( =G4*D4 for Visa)

google_docs5 Google finance and spreadsheets tutorial part 1

Now we can get Portfolio value by

=sum(I4:I6)

And total return:

=sum(H4:H6)

Percentage return can calculated by =

=B10/(sum(F4:F6))*100

Return in dollars divided with purchase cost and multiplied with 100

I got following:

google_docs7 Google finance and spreadsheets tutorial part 1

Lastly we can add something extra to it…

Click Visas cell C4 then click color fill icon

google_docs8 Google finance and spreadsheets tutorial part 1

and then conditional for mating

google_docs9 Google finance and spreadsheets tutorial part 1

There you can setup cell to change color based on it’s value. We want green when stock is up and red when it is down.

Now our simple portfolio watch should be done. You can view result here check if you missed something:

https://docs.google.com/spreadsheets/d/1_iQWC5nJA6O9_CVGG8z2LCEaatRata64Is18NzhKMNk/edit?usp=sharing

There are much more you can do with Google drive sheets.

Here is list of attributes you can get from google finance

price: market price of the stock.

  • priceopen: the opening price of the stock for the current day.

  • high: the highest price the stock traded for the current day.

  • low: the lowest price the stock traded for the current day.

  • volume: number of shares traded of this stock for the current day.

  • marketcap: the market cap of the stock.

  • tradetime: the last time the stock traded.

  • datadelay: the delay in the data presented for this stock using the googleFinance() function.

  • volumeavg: the average volume for this stock.

  • pe: the Price-to-Earnings ratio for this stock.

  • eps: the earnings-per-share for this stock.

  • high52: the 52-week high for this stock.

  • low52: the 52-week low for this stock.

  • change: the change in the price of this stock since yesterday’s market close.

  • beta: the beta value of this stock.

  • changepct: the percentage change in the price of this stock since yesterday’s close.

  • closeyest: yesterday’s closing price of this stock.

  • shares: the number of shares outstanding of this stock.

  • currency: the currency in which this stock is traded.

Continue to part 2

Keep on investing!

Leave a Reply