Tracking investments with Google Spreadsheet

[Some-one]

My Stock Portfolio Tracker uses both Google and Yahoo Finance data. Somehow Google Finance stopped working but the price on my Stock Portfolio Tracker Still updates.

Current Portfolio Case Study

To learn how to use, you can go to this Guide to the Free Google Stock Portfolio Tracker

Yahoo Finance would sometimes produce #value using your method. Is there a more stable data source?

 

[klarklar]

I wonder if this is SGX being narrow-minded and petty or Google's fault. Such price data should be provided free to encourage greater investor participation. SGX is too small a player to charge for data. Our market volume is already dying. I hope SGX realizes that.

 

[peterchan75]

Based on anecdotal evidence, it should be on Google Finance side. Yahoo Finance is working fine. Anyway, it's already up and running.

 

[klarklar]

Based on anecdotal evidence, it should be on Google Finance side. Yahoo Finance is working fine. Anyway, it's already up and running.

Based on error message

"Error
When evaluating GOOGLEFINANCE, Google Spreadsheets is not authorized to access data for exchange: 'SGX'"

, I think SGX blocked Google Finance

 

[greenbeam1980]

What is the code i need to use for BLDP in Nasdaq?

 

[greenbeam1980]

How to use yahoo finance instead? Can someone advise?

 

[peterchan75]

Here is an Excel VBA code for downloading open, high, low, close, volume data.
Insert Data label in Sheet1.
A1 - Yahoo Finance Stock Code
B1 - Open
C1 - High
D1 - Low
E1 - Close
D1 - Volume

E.g.
A2 - U11.SI
A3 - D05.SI
etc

This is the Excel VBA code for downloading o,h,l,c,v data.

Sub Get_Yahoo_Finance_data()

Dim i As Integer, j As Integer
Dim dl_url As String
Dim dl_url_stock As String
Dim dl_string As String
Dim stock_data As Variant
Dim WinHttpReq As Object

'Get the Yahoo Finance code from thie website http://www.financialwisdomforum.org/gummy-stuff/Yahoo-data.htm
dl_url = "http://finance.yahoo.com/d/quotes.csv?s=xxxx&f=ohgl1v"

Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")

For i = 1 To ThisWorkbook.Sheets("Sheet1").UsedRange.Rows.Count - 1

dl_url_stock = Replace(dl_url, "xxxx", ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(i, 0))

WinHttpReq.Open "GET", dl_url_stock, False
WinHttpReq.Send

dl_string = WinHttpReq.responseText

If Len(dl_string) > 0 Then
stock_data = Split(dl_string, ",")
For j = 0 To 4
ThisWorkbook.Sheets("Sheet1").Range("A1").Offset(i, j + 1) = Replace(stock_data(j), Chr(10), "")
Next j
End If

Next i

End Sub

 

[peterchan75]

Based on error message , I think SGX blocked Google Finance

This one... er... just a error message from Google Spreadsheet. Cannot be 100% sure that SGX blocks it.

 

[louist]

For me, I just want to pull the current price, so currently using this line:

=importData("http://finance.yahoo.com/d/quotes.csv?s=es3.SI&f=l1")
(Replace 'es3' with whatever stock you want)

It has the benefit of supporting more decimal places (relevant for the cheaper stocks).

p.s. The first post of this thread actually gives the same instruction

How to use yahoo finance instead? Can someone advise?

 

[greenbeam1980]

For me, I just want to pull the current price, so currently using this line:

=importData("http://finance.yahoo.com/d/quotes.csv?s=es3.SI&f=l1")
(Replace 'es3' with whatever stock you want)

It has the benefit of supporting more decimal places (relevant for the cheaper stocks).

p.s. The first post of this thread actually gives the same instruction

My bad. Now I knew it after i refer to the 1st post.

 

[greenbeam1980]

Hi, to get stock price for Singapore stock, we use this code:

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

If I want to get price from NYSE, nasdaqgm, nasdaqcm etc, what must I use?
I know .SI is for singapore stock exchange.

Pls advise.

 

[peterchan75]

Hi, to get stock price for Singapore stock, we use this code:

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

If I want to get price from NYSE, nasdaqgm, nasdaqcm etc, what must I use?
I know .SI is for singapore stock exchange.

Pls advise.

Just the stock code is enough. If in doubt, just try it. No harm.

 

[af7680]

For me, I just want to pull the current price, so currently using this line:

=importData("http://finance.yahoo.com/d/quotes.csv?s=es3.SI&f=l1")
(Replace 'es3' with whatever stock you want)

It has the benefit of supporting more decimal places (relevant for the cheaper stocks).

p.s. The first post of this thread actually gives the same instruction

Thank you and this is really helpful

 

[K|muRa^84]

Hi, to get stock price for Singapore stock, we use this code:

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

If I want to get price from NYSE, nasdaqgm, nasdaqcm etc, what must I use?
I know .SI is for singapore stock exchange.

Pls advise.

this has stopped wrking for me

anyone has the same prob ?

 

[linxiaobei]

Google finance is still not working for SGX. US market still okay.

Maybe they didn't pay them for live feed lol

Sent from Sun using GAGT

 

[Average]

I using google finance portfolio, np.

Sent from your next door using GAGT

 

[ValueInvestor]

i use this for sg stock prices

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

 

[linxiaobei]

Haha I also using yahoo but the price doesn't seems to be live leh. Like got delay.

Sent from Sun using GAGT

 

[linxiaobei]

I using google finance portfolio, np.

Sent from your next door using GAGT

As in using the formula inside the Google spreadsheet and it is still okay?

Apparently it is down for weeks already. But it is working normally for other markets.

Sent from Sun using GAGT

 

[ValueInvestor]

Haha I also using yahoo but the price doesn't seems to be live leh. Like got delay.

Sent from Sun using GAGT

ya delayed... free so bo bian