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

Rainbow1112

Supremacy Member
Joined
May 6, 2008
Messages
5,338
Reaction score
104
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.

cant seem to get this to work..

formula entered in cell G2

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

Cell F2 i enter Bloomberg code for M1 - M1:SP

i'm getting Error Imported content is empty.
 

Flex11

Master Member
Joined
Oct 4, 2002
Messages
3,174
Reaction score
0
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.

Thanks alot ! Works like a charm !
 

bobbytkc

Senior Member
Joined
Dec 24, 2005
Messages
1,584
Reaction score
34
cant seem to get this to work..

formula entered in cell G2

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

Cell F2 i enter Bloomberg code for M1 - M1:SP

i'm getting Error Imported content is empty.


Just key in M1, not M1:SP
 

apriliasiao

Supremacy Member
Joined
Dec 11, 2001
Messages
8,624
Reaction score
491
Just key in M1, not M1:SP

can you give an example of the working strings? i tried many combo but didnt work like that 1 below since :SP is not needed.

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

i have also tried putting M1 in F3 cell and your code =IMPORTXML("http://www.bloomberg.com/quote/"&F3&":SP","//div[@class='price']") in another cell.
Dint work too.

I notice that Bloomberg is using https instead of http which is in your code.
 
Last edited:

apriliasiao

Supremacy Member
Joined
Dec 11, 2001
Messages
8,624
Reaction score
491
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.

thanks but i have error using your Singtel example.
 

peterchan75

Supremacy Member
Joined
Apr 26, 2003
Messages
6,467
Reaction score
447
can you give an example of the working strings? i tried many combo but didnt work like that 1 below since :SP is not needed.

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

I tested this one.
=IMPORTXML(ʺhttp://www.bloomberg.com/quote/M1:SPʺ,ʺ//span[@class='priceText__1853e8a5']ʺ)

This http://www.bloomberg.com/quote/M1:SP should take you to the Bloomberg M1 price page. Right click on the price and select Inspect Element and you should be able to see the price in span class.

Use Google Finance instead as the stock code is same as SGX.
=ImportXML(ʺhttp://www.google.com/finance?q=SGX:Z74ʺ,ʺ//span[@class='pr']ʺ)

For some unknown reason, you cannot cut & paste the formula from this forum into Google sheet. You must retype into your Google sheet.

Excel macro to download Yahoo Finance stock price. For SGX stock code, must add ".SI" at the end of symbol.

Sub yahoo()
'Ticker symbol starts from row 2 of column A of Sheet1
'Price of corresponding ticker is in column B

ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = "Ticker"
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = "Price"

' open IE, navigate to the website of interest and loop until fully loaded

For i = 2 To ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Set IE = CreateObject("InternetExplorer.Application")
my_url = "https://finance.yahoo.com/quote/" & ThisWorkbook.Sheets("Sheet1").Cells(i, 1) & "?p=" & ThisWorkbook.Sheets("Sheet1").Cells(i, 1)

With IE
.Visible = False
.navigate my_url
.Top = 50
.Left = 530
.Height = 400
.Width = 400

Do Until Not IE.Busy And IE.readyState = 4
DoEvents
Loop
End With

ThisWorkbook.Sheets("Sheet1").Cells(i, 2) = IE.document.getElementById("quote-header-info").getElementsByTagName("span")(3).innerText

IE.Quit
Set IE = Nothing
Next i

End Sub
 
Last edited:

apriliasiao

Supremacy Member
Joined
Dec 11, 2001
Messages
8,624
Reaction score
491
siew man got it working now.. can explain why class='priceText__1853e8a5' where else bobbytkc is using class='price'?
 

Perisher

Greater Supremacy Member
Deluxe Member
Joined
Jan 5, 2015
Messages
84,276
Reaction score
10,137
I tested this one.
=IMPORTXML(ʺhttp://www.bloomberg.com/quote/M1:SPʺ,ʺ//span[@class='priceText__1853e8a5']ʺ)

This http://www.bloomberg.com/quote/M1:SP should take you to the Bloomberg M1 price page. Right click on the price and select Inspect Element and you should be able to see the price in span class.

Use Google Finance instead as the stock code is same as SGX.
=ImportXML(ʺhttp://www.google.com/finance?q=SGX:Z74ʺ,ʺ//span[@class='pr']ʺ)

For some unknown reason, you cannot cut & paste the formula from this forum into Google sheet. You must retype into your Google sheet.

Not sure if it's related but google finance is gonna revamp by mid-Nov so it might be down for a long time.
 

peterchan75

Supremacy Member
Joined
Apr 26, 2003
Messages
6,467
Reaction score
447
Not sure if it's related but google finance is gonna revamp by mid-Nov so it might be down for a long time.

I suspect this forum attach some hidden characters that Google sheet cannot digest when we cut & paste from this forum to Google sheet. If Google go by the Yahoo way, importxml will not work in the future.
 

apriliasiao

Supremacy Member
Joined
Dec 11, 2001
Messages
8,624
Reaction score
491
thanks all for the help. manage to dump all yahoo in place of WSJ and Bloomberg quotes.
 

TinyPocoyo

Arch-Supremacy Member
Joined
Apr 12, 2010
Messages
12,006
Reaction score
22
i may have missed out some investment secret.. can anyone explain to me why must use so many pattern to see stock quote?

i thought your broking house should provide you platform to see stock quote?
 

angtc11

Member
Joined
Feb 14, 2004
Messages
469
Reaction score
5
i may have missed out some investment secret.. can anyone explain to me why must use so many pattern to see stock quote?

i thought your broking house should provide you platform to see stock quote?

For me, it's to see the value of my investments at a single place without having to key in the stock prices. I only go to broker site only once in a while and only to trade
 

apriliasiao

Supremacy Member
Joined
Dec 11, 2001
Messages
8,624
Reaction score
491
ya. all key in siew siew in portfolio so cant auto calculate all profits/loss.
 

Tornesoul

Master Member
Joined
Nov 29, 2008
Messages
3,381
Reaction score
8
hey guys, ive been using the Singapore Stock Viewer app on my hp to see daily prices. It stopped working after yahoo finance stopped.

Any alternatives u guys r using that r still working?
 

evilgod08

Senior Member
Joined
Nov 6, 2000
Messages
1,389
Reaction score
2
I suspect this forum attach some hidden characters that Google sheet cannot digest when we cut & paste from this forum to Google sheet. If Google go by the Yahoo way, importxml will not work in the future.

Found the reason. The quotes used in this forum is not recognized by Google. Replace with this " and it should work in google sheets

Anyways, the Bloomberg formula works for me but not google finance. Is this the same for everyone?
 

cwm83

Senior Member
Joined
Sep 22, 2000
Messages
1,326
Reaction score
1
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.

do you know if its possible to get change, 52wk low and high?
 

muthuma

Junior Member
Joined
Jun 5, 2018
Messages
33
Reaction score
10
Hi,
Thanks for your comments.

WSJ works.

I tried the Bloomberg. Does nto work

Pls help. Thanks.
 
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