Google spreadsheets as investment tool tutorial part 2

Google spreadsheets is online tool that you can do similar calculation as in Microsoft Excel. Because Google spreadsheets 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. This is part 2 where we go further into google sheets with history graph drawing and using simple script.

Part 1 is here

Last time we got here with Google spreadsheets

So this is what we did last time

google2_part1_ending Google spreadsheets as investment tool tutorial part 2

We will continue from there and full file of previous work is available here:

https://docs.google.com/spreadsheets/d/1_iQWC5nJA6O9_CVGG8z2LCEaatRata64Is18NzhKMNk/edit#gid=0

So save this file to your own Google drive so you can edit it(if you did not do the file step by step last time)

Let’s make new sheet into the file for historic data

New sheet can be added with plus sign in lower left corner

google2_new_sheet Google spreadsheets as investment tool tutorial part 2

Name the sheet as draw_chart

Add following cell texts:

google2_draw_chart2 Google spreadsheets as investment tool tutorial part 2

Ticker is here to select which stock price to pick. Dates mark the period to draw.

Instead of just writing the dates use these(without quotation marks):

For from date: “=today()-27”

For to date:”=today()”

This is how we get period of 27 days to today.

Code to get historical data

Put following code to L4

=GOOGLEFINANCE(B3, “price”, C3, D3, “DAILY”)

This gets us the price date with daily interval.

Data should print just below L4

google2_price_data Google spreadsheets as investment tool tutorial part 2

You could draw chart to here but let’s get back to first sheet.

First sheet editing

Rename first sheet from “Sheet1” to “main“

Let’s draw chart here instead

From top menus click

Insert→Chart

Add “draw_chart!L4:M1000” source to input box

google2_draw_chart3 Google spreadsheets as investment tool tutorial part 2
Select some chart type (I have line chart selected)

google2_chart_drawn Google spreadsheets as investment tool tutorial part 2

We have the chart…Great but it is not very nice to use if you want to alternate between different stocks.

Let’s make a button for this job.

Click Insert->drawing

Here first make shape and then put text box inside it. Something like this

button Google spreadsheets as investment tool tutorial part 2

Ok, we have button but it does not do anything. A script is needed for this

Make a script to command our chart tool

Click Tools →Script editor

Copy this to there and save it as “click_draw” (without quote signs):

function click_draw() {

//identify which sheet/file is open and save this info as variable ss

var ss = SpreadsheetApp.getActiveSpreadsheet();

//get sheets also as variables from the file

var s2 = ss.getSheetByName(“draw_chart”);

var s1 = ss.getSheetByName(“main”);

//get cell location which is selected by user

var cell= s1.getActiveRange();

//Make input box(where ticker goes) location cell as variable

var drawinput = s2.getRange(3,2);

//Get value from user selected cell and save it to as ticker

drawinput.setValue(cell.getValue())

}

As you can see from commented code, the code changes the ticker name in chart. The actual chart updates automatically when script is ran.

The next job is to assign this to the button we made earlier.

draw_assign Google spreadsheets as investment tool tutorial part 2

Now everything should be ready.

Now just select ticker by from the list by clicking it and then click draw button.

draw_how_touse Google spreadsheets as investment tool tutorial part 2
So there you have it. A chart tool that you just made by your self. If something is wrong use this ready made version:

https://docs.google.com/spreadsheets/d/1W7T16g4AvQEZ3eHVpCDr3U2GHTwb2CHUV7Y9R-I_tzE/edit?usp=sharing

(just save it first to your Google drive so you can edit it)

So go and use your imagination with Google Spreadsheets and Google Finance and send me what cool things you can make with it 🙂

And keep on investing!

Leave a Reply