Tracking investments with Google Spreadsheet

G

genie47

High Supremacy Member
Joined
Jan 1, 2000
Messages
27,508
Reaction score
37
I know this has been flogged many times and thanks to Drizzt and his spreadsheets, I've dissected inner workings on how things are done. So just a few things for you newbies to play around to track your stock.

Firstly, Google Finance integrates well with Google Spreadsheet.

To call up the last traded price of a stock, input this into a cell. I will use SPDR STI ETF as an example.

=GoogleFinance("SGX:ES3";"price")

This will call out the latest price for ES3 (SPDR STI ETF listed on SGX).

There are a lot of other things you can call up from Google Finance to Google Spreadsheet. For example, the currency conversion. For example USD to SGD since you bought some USD denominated stock. So you input this into a cell:

=googlefinance("CURRENCY:USDSGD")

This is great. But some of us are buying SGS Bonds directly and Google Finance does not have the quotes for the PH1S 30 year SGS bond. How? Yahoo Finance has the quote for PH1S. You input this into a cell.

=importData("http://finance.yahoo.com/d/quotes.csv?s=PH1S.SI&f=l1")

The crucial info is the "&f" you see after the stock code. "l1" pulls out the last traded price for the stock. For more of the &f string, see this website.

https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty

Try these out when constructing your own spreadsheet to track your investments.
 
yummyfei

yummyfei

Senior Member
Joined
Sep 28, 2005
Messages
2,081
Reaction score
3
but googlefinance can only show 2 decimal points, good for US stocks but sg stocks need 3decimal points... end up have to pull data from yahoo finance..

or is there a way to force googlefinance to show 3 decimal points?
 
L

lzydata

Supremacy Member
Joined
Oct 16, 2010
Messages
6,658
Reaction score
2,968
THIS. IS. AWESOME. I didn't know :( Best thing I have learnt here.

Btw, for some reason Singtel doesn't work? Z74. It loads fine on Google Finance itself though.
 
L

lzydata

Supremacy Member
Joined
Oct 16, 2010
Messages
6,658
Reaction score
2,968
yummyfei said:
but googlefinance can only show 2 decimal points, good for US stocks but sg stocks need 3decimal points... end up have to pull data from yahoo finance..

or is there a way to force googlefinance to show 3 decimal points?
Click to expand...

I found a way.

=index(GoogleFinance("SGX:C38U", "close", today()-1, today(), "1"), 2, 2)

This will also generate 3 other formula cells surrounding this cell, but you can override them.
 
G

genie47

High Supremacy Member
Joined
Jan 1, 2000
Messages
27,508
Reaction score
37
Got more. Great for those who do backtesting of certain portfolio setups.

Try this out.

=GoogleFinance("SGX:ES3","price","2/1/2013",TODAY())

What it does is bring out the price (last done) of SPDR STI ETF from 1st Feb 2013 (American date format month/day/year) till today. The TODAY function has nothing inside so you have to end it with ().

Wham! you will get this:

Code: 
Date                 Close
2/4/2013 17:00:00	3.31
2/5/2013 17:00:00	3.3
2/6/2013 17:00:00	3.29
2/7/2013 17:00:00	3.27
2/8/2013 17:00:00	3.29
2/13/2013 17:00:00	3.33
2/14/2013 17:00:00	3.3
2/15/2013 17:00:00	3.29
2/18/2013 17:00:00	3.3
..
..
..
6/19/2013 17:00:00	3.27
6/20/2013 17:00:00	3.22
6/21/2013 17:00:00	3.2
6/24/2013 17:00:00	3.18
6/25/2013 17:00:00	3.15
6/26/2013 17:00:00	3.14
6/27/2013 17:00:00	3.19
6/28/2013 17:00:00	3.21
 
D

digitalguy

Senior Member
Joined
Nov 2, 2004
Messages
1,006
Reaction score
1
some times google doesnt pull the data properly (atleast to me), either it N/A or wrong price. find yahoo is quite reliable.:s12:
 
Sinkie

Sinkie

Greater Supremacy Member
Joined
Jan 20, 2009
Messages
86,040
Reaction score
20
digitalguy said:
some times google doesnt pull the data properly (atleast to me), either it N/A or wrong price. find yahoo is quite reliable.:s12:
Click to expand...

Yeah, when I tried to use google API to pull t39, sph but instead they returns a dont know simi France company price

However google feed is live price for Sgx iirc
 
G

genie47

High Supremacy Member
Joined
Jan 1, 2000
Messages
27,508
Reaction score
37
OK for a tables challenge. I use spot price of gold.

When looking for a table, look for one with a simple setup.

I know gold is traded in USD but you just want to know its value in SGD. So this is the webby I take the data from.

Gold and Silver Price Today in Singapore in Singapore Dollar (SGD) | Gold Rate 24

So in the cell, you key in this:
Code: 
=Index(ImportHtml("http://www.goldrate24.com/gold-prices/asia/singapore/"; "table";1),2,2)

So what it tells the cell to do is go to the website. Import table 1. Take data from column 2, row 2.

This pulls out the spot price of gold in SGD for 1 t oz of gold.
 
eveee99

eveee99

Senior Member
Joined
May 15, 2013
Messages
1,314
Reaction score
0
genie47 said:
I know this has been flogged many times and thanks to Drizzt and his spreadsheets, I've dissected inner workings on how things are done. So just a few things for you newbies to play around to track your stock.

Firstly, Google Finance integrates well with Google Spreadsheet.

To call up the last traded price of a stock, input this into a cell. I will use SPDR STI ETF as an example.

=GoogleFinance("SGX:ES3";"price")



This will call out the latest price for ES3 (SPDR STI ETF listed on SGX).

There are a lot of other things you can call up from Google Finance to Google Spreadsheet. For example, the currency conversion. For example USD to SGD since you bought some USD denominated stock. So you input this into a cell:

=googlefinance("CURRENCY:USDSGD")

This is great. But some of us are buying SGS Bonds directly and Google Finance does not have the quotes for the PH1S 30 year SGS bond. How? Yahoo Finance has the quote for PH1S. You input this into a cell.

=importData("http://finance.yahoo.com/d/quotes.csv?s=PH1S.SI&f=l1")

The crucial info is the "&f" you see after the stock code. "l1" pulls out the last traded price for the stock. For more of the &f string, see this website.

https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty

Try these out when constructing your own spreadsheet to track your investments.
Click to expand...

I tried to put =importData("http://finance.yahoo.com/d/quotes.csv?s=PH1S.SI&f=l1") into a excel file but it didn't work. Can't pull out the last traded price. can anyone else help? thks!
 
A

AlphaQuant

Junior Member
Joined
Mar 7, 2013
Messages
33
Reaction score
0
eveee99 said:
I tried to put =importData("http://finance.yahoo.com/d/quotes.csv?s=PH1S.SI&f=l1") into a excel file but it didn't work. Can't pull out the last traded price. can anyone else help? thks!
Click to expand...

you need to use Google Spreadsheets.
 
G

genie47

High Supremacy Member
Joined
Jan 1, 2000
Messages
27,508
Reaction score
37
eveee99 said:
I tried to put =importData("http://finance.yahoo.com/d/quotes.csv?s=PH1S.SI&f=l1") into a excel file but it didn't work. Can't pull out the last traded price. can anyone else help? thks!
Click to expand...

This only works on Google Spreadsheet. Not Excel. Excel is another thing altogether.
 
eveee99

eveee99

Senior Member
Joined
May 15, 2013
Messages
1,314
Reaction score
0
lzydata said:
I found a way.

=index(GoogleFinance("SGX:C38U", "close", today()-1, today(), "1"), 2, 2)

This will also generate 3 other formula cells surrounding this cell, but you can override them.
Click to expand...

I tried using the above formula in google spreadsheet but cant get 3 decimals. Am I supposed to put something inside the () sign?
 
G

genie47

High Supremacy Member
Joined
Jan 1, 2000
Messages
27,508
Reaction score
37
eveee99 said:
I tried using the above formula in google spreadsheet but cant get 3 decimals. Am I supposed to put something inside the () sign?
Click to expand...

Highlight cell. Go to the menu bar. Format>Number>Custom Decimals.

Then again Format>Number>Currency or Financial.

Yes must do two times. It just stacks on top and does not cancel the other out. To cancel you have to Format>Clear Formatting.
 
Sinkie

Sinkie

Greater Supremacy Member
Joined
Jan 20, 2009
Messages
86,040
Reaction score
20
Sinkie said:
Yeah, when I tried to use google API to pull t39, sph but instead they returns a dont know simi France company price

However google feed is live price for Sgx iirc
Click to expand...

can anyone help me on this? :D

thanks..

using excel, dunno why i tried to pull out sph, ended up some french company
 
Sinkie

Sinkie

Greater Supremacy Member
Joined
Jan 20, 2009
Messages
86,040
Reaction score
20
AlphaQuant said:
seems fine for me - type =googlefinance("SGX:T39") and i see 4.29
Click to expand...

eh, nope, im wrote a module to import streaming feed from google into microsoft excel (not google spreadsheet), but when i try to pull in t39, it shows the other px, but for rest of the sgx prices, its normal, just that sph got problem.

strange
 
S

Some-one

Great Supremacy Member
Joined
Jan 1, 2000
Messages
52,838
Reaction score
3,811
Well, Google Spreadsheet is really good. I wrote my own codes to track my portfolio vs STI. The finance information is scheduled to be placed into the cells daily after 6pm.

You all should really try it. That is how you can improve your returns. Here is my results so far this year. :s22:

Dividends not included.

 
Last edited:
You must log in or register to reply here.
Important Forum Advisory Note
This forum is moderated by volunteer moderators who will react only to members' feedback on posts. Moderators are not employees or representatives of HWZ Forums. Forum members and moderators are responsible for their own posts. Please refer to our Community Guidelines and Standards and Terms and Conditions for more information.
Top