What alternatives after Yahoo Finance stops intra-day stock quotes?

Jumpman23

Supremacy Member
Joined
Jan 1, 2000
Messages
5,555
Reaction score
10
did u manage to find a way to feed in SGX stock into google sheet? can share?

I used:

=arrayformula(ImportData("http://finance.google.com/finance/getprices?x=SGX&q=Z74&i=60&p=1d"))

to get minute update of intraday prices for 1 day at every manual refresh (i think creating a auto refresh script in Sheets might work as well)

In another cell I simply lookup the last entry of the above to get the current price:

=INDEX(FILTER(N:N,NOT(ISBLANK(N:N))),ROWS(FILTER(N:N,NOT(ISBLANK(N:N)))))

where N is the column for the close price

Hope this helps.
 

Layers

Arch-Supremacy Member
Joined
Aug 12, 2004
Messages
16,927
Reaction score
0
I used:

=arrayformula(ImportData("http://finance.google.com/finance/getprices?x=SGX&q=Z74&i=60&p=1d"))

to get minute update of intraday prices for 1 day at every manual refresh (i think creating a auto refresh script in Sheets might work as well)

In another cell I simply lookup the last entry of the above to get the current price:

=INDEX(FILTER(N:N,NOT(ISBLANK(N:N))),ROWS(FILTER(N:N,NOT(ISBLANK(N:N)))))

where N is the column for the close price

Hope this helps.
How about excel?

Sent from Sony E6853 using GAGT
 

mrwirus

Senior Member
Joined
Jun 8, 2006
Messages
1,868
Reaction score
13
last time i used this to get the price from yahoo finance.. anyone knows how to just get the same quote using google?

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

I only need that one value..
 

bobbytkc

Senior Member
Joined
Dec 24, 2005
Messages
1,584
Reaction score
34
Hi all,

I have 2 alternatives if you are using Investment Moat's google spreadsheet.

Simply copy and paste the following into the respective cells:

For Bloomberg Prices:

=IMPORTXML("http://www.bloomberg.com/quote/"&F3&":SP","//div[@class='price']")

Replace the cell F3 with Bloombergs code for the SGX listed company. For instance, if you look up Keppel Corp, Bloomberg's code for keppel is KEP, so key in KEP in the cell F3.

For wall street journal prices:

=IMPORTXML("http://quotes.wsj.com/SG/"&F3&"","//span[@class='cr_num cr_curr_price']/span[@id='quote_val']")

WSJ is easier, because you just need to put in the SGX ticker in F3. For instance, the ticker for Kep Corp is BN4, so key in BN4 in the cell F3.

For some reason, quotes for the ETF G3B is not available on WSJ, so I had to use Bloomberg's prices for that. I only tested the above on google spreadsheets, not excel. I have no idea how to do it for excel.
 
Last edited:

mrwirus

Senior Member
Joined
Jun 8, 2006
Messages
1,868
Reaction score
13
Hi all,

I have 2 alternatives if you are using Investment Moat's google spreadsheet.

Simply copy and paste the following into the respective cells:

For Bloomberg Prices:

=IMPORTXML("http://www.bloomberg.com/quote/"&F3&":SP","//div[@class='price']")

Replace the cell F3 with Bloombergs code for the SGX listed company. For instance, if you look up Keppel Corp, Bloomberg's code for keppel is KEP, so key in KEP in the cell F3.

For wall street journal prices:

=IMPORTXML("http://quotes.wsj.com/SG/"&F3&"","//span[@class='cr_num cr_curr_price']/span[@id='quote_val']")

WSJ is easier, because you just need to put in the SGX ticker in F3. For instance, the ticker for Kep Corp is BN4, so key in BN4 in the cell F3.

For some reason, quotes for the ETF G3B is not available on WSJ, so I had to use Bloomberg's prices for that. I only tested the above on google spreadsheets, not excel. I have no idea how to do it for excel.
I'm trying to use your method but it kept giving me an error called "unknown range name".. or "Imported content is empty." Did I do something wrong?
 

bobbytkc

Senior Member
Joined
Dec 24, 2005
Messages
1,584
Reaction score
34
I'm trying to use your method but it kept giving me an error called "unknown range name".. or "Imported content is empty." Did I do something wrong?

Maybe You can post a screenshot of your spreadsheet and error? It is working on my spreadsheet.

In the code, you need to key in the ticker into a cell, and then replace the F3 with the coordinate of the cell where you keyed in the ticker.
 

mrwirus

Senior Member
Joined
Jun 8, 2006
Messages
1,868
Reaction score
13
Maybe You can post a screenshot of your spreadsheet and error? It is working on my spreadsheet.

In the code, you need to key in the ticker into a cell, and then replace the F3 with the coordinate of the cell where you keyed in the ticker.
ohhh.. I got it working.. Maybe I very manual.. haha.. every cell I will key in the ticker manually..

=IMPORTXML("http://quotes.wsj.com/SG/AJBU","//span[@class='cr_num cr_curr_price']/span[@id='quote_val']")

This will get Keppel DC Reit pricing.. Now I know..

Thanks alot for all your help!
 

mrwirus

Senior Member
Joined
Jun 8, 2006
Messages
1,868
Reaction score
13
Maybe You can post a screenshot of your spreadsheet and error? It is working on my spreadsheet.

In the code, you need to key in the ticker into a cell, and then replace the F3 with the coordinate of the cell where you keyed in the ticker.
I can't get it working for bonds.. Does WSJ provide bond prices?
 

mrwirus

Senior Member
Joined
Jun 8, 2006
Messages
1,868
Reaction score
13
I haven't tested it for bonds as I don't own any so I can't comment.

You can try to look it up using Bloomberg.
can't seem to find it.. sigh.. anyone can help?

I dun need live data.. but i dun even know how to get like maybe end of day data..
 

Mad_Stranger

Member
Joined
Nov 20, 2011
Messages
215
Reaction score
0
Thanks for the workaround!
One thing to add. it seems that WSJ prices are rounded to nearest 2 decimal places. This will be a problem for some of the stocks below $2


Hi all,

I have 2 alternatives if you are using Investment Moat's google spreadsheet.

Simply copy and paste the following into the respective cells:

For Bloomberg Prices:

=IMPORTXML("http://www.bloomberg.com/quote/"&F3&":SP","//div[@class='price']")

Replace the cell F3 with Bloombergs code for the SGX listed company. For instance, if you look up Keppel Corp, Bloomberg's code for keppel is KEP, so key in KEP in the cell F3.

For wall street journal prices:

=IMPORTXML("http://quotes.wsj.com/SG/"&F3&"","//span[@class='cr_num cr_curr_price']/span[@id='quote_val']")

WSJ is easier, because you just need to put in the SGX ticker in F3. For instance, the ticker for Kep Corp is BN4, so key in BN4 in the cell F3.

For some reason, quotes for the ETF G3B is not available on WSJ, so I had to use Bloomberg's prices for that. I only tested the above on google spreadsheets, not excel. I have no idea how to do it for excel.
 

chopra

High Supremacy Member
Joined
Apr 15, 2003
Messages
49,479
Reaction score
472
interday good enough for me as i dont scalp.
yahoo finance still working for users like me
 

stjoe1

Member
Joined
Dec 22, 2005
Messages
471
Reaction score
3
can't seem to find it.. sigh.. anyone can help?

I dun need live data.. but i dun even know how to get like maybe end of day data..

Yes bonds and pref shares seem not available from those sources..

I am using Shareinvestors e.g for DBS pref shares..

https://www.shareinvestor.com/fundamental/factsheet.html?counter=MU7.SI
 

stjoe1

Member
Joined
Dec 22, 2005
Messages
471
Reaction score
3
you meant you just go into the website and take a look manually right?

No use web scraping formula.. Importxml to get the data directly to googlesheet..

You can use this formula to get the last done price for FCL bond with stock code AXXZ. For other bonds/pref shares, just change AXXZ with their stock codes.

=ImportXML("http://www.shareinvestor.com/fundamental/factsheet.html?counter=AXXZ", "//td[@class='sic_lastdone']/strong")
 
Last edited:

appl888

Supremacy Member
Joined
Nov 9, 2017
Messages
7,646
Reaction score
0
Can try cnbc app
It has 3 digits
E.g. 0.535
1.38 but not 1.385

I also use SGX mobile app
 
Last edited:

Flex11

Master Member
Joined
Oct 4, 2002
Messages
3,174
Reaction score
0
Hi all,

I have 2 alternatives if you are using Investment Moat's google spreadsheet.

Simply copy and paste the following into the respective cells:

For Bloomberg Prices:

=IMPORTXML("http://www.bloomberg.com/quote/"&F3&":SP","//div[@class='price']")

Replace the cell F3 with Bloombergs code for the SGX listed company. For instance, if you look up Keppel Corp, Bloomberg's code for keppel is KEP, so key in KEP in the cell F3.

For wall street journal prices:

=IMPORTXML("http://quotes.wsj.com/SG/"&F3&"","//span[@class='cr_num cr_curr_price']/span[@id='quote_val']")

WSJ is easier, because you just need to put in the SGX ticker in F3. For instance, the ticker for Kep Corp is BN4, so key in BN4 in the cell F3.

For some reason, quotes for the ETF G3B is not available on WSJ, so I had to use Bloomberg's prices for that. I only tested the above on google spreadsheets, not excel. I have no idea how to do it for excel.

Thanks for the detailed writeup.

Any idea what stock code should I put for US or London stocks? Such as IWDA? I tried afew combinations but couldn't get it to work
 

bobbytkc

Senior Member
Joined
Dec 24, 2005
Messages
1,584
Reaction score
34
Thanks for the detailed writeup.

Any idea what stock code should I put for US or London stocks? Such as IWDA? I tried afew combinations but couldn't get it to work

For markets, you just look up the relevant stock and look for the corresponding code for international markets.

For instance, if you google AAPL bloomberg, it returns "https://www.bloomberg.com/quote/AAPL:US"

So you just replace ":SP" to ":US" for it to work for US equities.

For WSJ, you can so the same, and replace the SG with the corresponding one for international markets.
 
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. Forum members and moderators are responsible for their own posts.

Please refer to our Community Guidelines and Standards, Terms of Service and Member T&Cs for more information.
Top