How to Retrieve Stock Market Quotations into Excel

[DRSteele]

One can get a huge variety of quotations from Yahoo! and download them into Excel by using Excel’s slick function WEBSERVICE combined with some text concatenation.

Here is a brief example of how to do this. You need to know the symbol that Yahoo assigns to each specific instrument and its market code. You can use the search features within the yahoo website to ascertain what you need; sometimes it takes some digging to find the correct symbols for obscure instruments, mutual funds and emerging markets. You also need to know the code for each piece of info you seek, like the LastTradeDate or LastClose. This website summarises the syntax, codes and references: Usingthe Yahoo Finance API for CSV « Jarloo

I chose to retrieve from yahoo the Name, LastTradeDate and LastTradePrice by using the codes n, d1 and l1. Simply extend right-wards for as many codes as you wish to retrieve for as many instruments as you wish, extended down-wards.

N.B. Update is not automatic, but pressing Ctrl+Alt+F9 is not much of a burden. Most quotes are delayed by at least 15 minutes; mutual funds are generally delayed by a day or two. Function WEBSERVICE often reports what look like numbers but are in fact some form of text; use of function NUMBERVALUE will convert to proper format. A worksheet full of hundreds of market symbols will indeed function, but sluggishly.

I hope this helps you.

	A	B	C	D
EURMAD=X
USDCAD=x
CNQ.to
BBU-UN.to
MSFT
XOM
CL=F
NG=F
RBCGLOBALENE.to

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #D9E1F2, align: center"]STOCK DATA FROM WEB FUNCTION[/TD]
[TD="bgcolor: #D9E1F2, align: center"][/TD]
[TD="bgcolor: #D9E1F2, align: center"][/TD]
[TD="bgcolor: #D9E1F2, align: center"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #BDD7EE"]press together CTRL + ALT + f9 to refresh[/TD]
[TD="bgcolor: #BDD7EE, align: right"][/TD]
[TD="bgcolor: #BDD7EE, align: right"][/TD]
[TD="bgcolor: #BDD7EE, align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #BDD7EE"][/TD]
[TD="bgcolor: #BDD7EE, align: center"]Name[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Last Trade Date[/TD]
[TD="bgcolor: #BDD7EE, align: center"]Last Trade
(Price Only)[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFF2CC"]code:[/TD]
[TD="bgcolor: #FFF2CC, align: center"]n[/TD]
[TD="bgcolor: #FFF2CC, align: center"]d1[/TD]
[TD="bgcolor: #FFF2CC, align: center"]l1[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: #E2EFDA"]"EUR/MAD"
[/TD]
[TD="bgcolor: #E2EFDA"]"1/9/2017"
[/TD]
[TD="bgcolor: #E2EFDA, align: right"]10.67[/TD]

[TD="align: center"]6[/TD]

[TD="bgcolor: #E2EFDA"]"USD/CAD"
[/TD]
[TD="bgcolor: #E2EFDA"]"1/9/2017"
[/TD]
[TD="bgcolor: #E2EFDA, align: right"]1.32[/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #E2EFDA"]"CDN NATURAL RES"
[/TD]
[TD="bgcolor: #E2EFDA"]"1/9/2017"
[/TD]
[TD="bgcolor: #E2EFDA, align: right"]41.89[/TD]

[TD="align: center"]8[/TD]

[TD="bgcolor: #E2EFDA"]"BROOKFIELD BUSINESS PARTNERS LP"
[/TD]
[TD="bgcolor: #E2EFDA"]"1/9/2017"
[/TD]
[TD="bgcolor: #E2EFDA, align: right"]32.28[/TD]

[TD="align: center"]9[/TD]

[TD="bgcolor: #E2EFDA"]"Microsoft Corporation"
[/TD]
[TD="bgcolor: #E2EFDA"]"1/9/2017"
[/TD]
[TD="bgcolor: #E2EFDA, align: right"]62.73[/TD]

[TD="align: center"]10[/TD]

[TD="bgcolor: #E2EFDA"]"Exxon Mobil Corporation Common "
[/TD]
[TD="bgcolor: #E2EFDA"]"1/9/2017"
[/TD]
[TD="bgcolor: #E2EFDA, align: right"]87.18[/TD]

[TD="align: center"]11[/TD]

[TD="bgcolor: #E2EFDA"]"Light Sweet Crude Oil Futures,M"
[/TD]
[TD="bgcolor: #E2EFDA"]"1/9/2017"
[/TD]
[TD="bgcolor: #E2EFDA, align: right"]52.78[/TD]

[TD="align: center"]12[/TD]

[TD="bgcolor: #E2EFDA"]"Natural Gas Mar 17"
[/TD]
[TD="bgcolor: #E2EFDA"]"1/9/2017"
[/TD]
[TD="bgcolor: #E2EFDA, align: right"]3.12[/TD]

[TD="align: center"]13[/TD]

[TD="bgcolor: #E2EFDA"]"RBC Global Energy Sr A"
[/TD]
[TD="bgcolor: #E2EFDA"]"1/6/2017"
[/TD]
[TD="bgcolor: #E2EFDA, align: right"]44.88[/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]B5[/TH]
[TD="align: left"]=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&$A5&"&f="&B$4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&$A5&"&f="&C$4)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D5[/TH]
[TD="align: left"]=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s="&$A5&"&f="&D$4))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

_{^{<strike>
</strike>}}

 

[sbsecord]

Hi,
Is there a limit? I built a SS with my portfolio, but it seems after a certain # of stocks it stopped returning results for any additional entries. Thank you in advance!

 

[DRSteele]

Hi. I don't know whether there is a limit or not. I was able to create a worksheet with symbols for the entirety of the TMX, which is a large list.

 

[DRSteele]

Yahoo has amended its address. The web address must be like this, now including the phrase download.

WEBSERVICE("http://download.finance.yahoo.com/d/quotes.csv?s=CNQ.to&f=l1")

That example will get you the latest quote for Canadian Natural Resource on the TSX.

I added this info to this thread too. https://www.mrexcel.com/forum/excel...cal-stock-query-stopped-working-5-9-17-a.html