I hadn't seen MSNStockQuote give 3dp but if you've seen it clearly it does.
Similarly I had only seen the WebQuery return 2DP. You can enter the
webQuery address into your browser, if it only retuns 2dp there's nothing
you can do to get more. It's not a cell formatting issue.


Just messing around I put this Yahoo query together. Run each of the three
routines in order as posted.

Sub SampleStocks()
Dim ws As Worksheet

Set ws = ActiveWorkbook.Worksheets.Add
ws.Name = "Stocks"

ws.Range("a2") = "AZZ.AX"
ws.Range("a3") = "MSFT"
ws.Range("a4") = "RIO.L" ' RioTinto UK
End Sub


Sub AddYahooQT()
Dim ws As Worksheet

Set ws = ActiveWorkbook.Worksheets.Add
ws.Name = "YahooQuery"

With ws.QueryTables.Add(Connection:= _
"URL;http://finance.yahoo.com/q?s=MSFT", _
Destination:=Range("B1"))
.Name = "YahooStockQuery"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False ' << not default
.RefreshStyle = xlOverwriteCells '<< not default
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """table1"",""table2"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub


Sub UpdateWQ()
Dim nRow As Long, cnt As Long, i As Long
Dim qt As QueryTable
Dim rlabels As Range, rValues, rTick As Range
Dim rSymbols As Range, rCell As Range
Const cURL As String = "URL;http://uk.finance.yahoo.com/q?s="

With Worksheets("YahooQuery")
Set qt = .QueryTables("YahooStockQuery")
Set rlabels = .Range("B1:B17")
Set rValues = .Range("C1:C17")
Set rTick = .Range("A1")
End With

With Worksheets("Stocks")
Set rSymbols = .Range("A2")
cnt = .Range("A65536").End(xlUp).Row
.Range("B1").Resize(, rlabels.Count) = _
Application.Transpose(rlabels)
End With

cnt = cnt - rSymbols.Row + 1
Set rSymbols = rSymbols.Resize(cnt)

For Each rCell In rSymbols

qt.Connection = cURL & rCell
qt.WebTables = """table1"",""table2"""

qt.BackgroundQuery = False
qt.Refresh

For i = 1 To rValues.Count
rCell.Offset(, i) = rValues(i)
Next
Next

End Sub


For your purposes you won't want all the details returned as in the above,
rewrite the last loop to your needs, and add the appropriate labels.

Regards,
Peter T


"Ken G" wrote in message
...
Hello again.

Seems to be some confusion re my original post.
If I use this formula and the Stock Quote add-in ..
=MSNStockQuote($A2,"Last Price","AU") where $A2 contains the code "AZZ"
and
the target cell is formatted as a number with 3 decimal places I get 0.635
for the last sale today (21 Jan) which agrees with the Yahoo site and the
ASX
which is correct. However the web query through MoneyCentral only returns
2
decimal places. I was trying to find out how to get it to return 3 decimal
places but it seems that's not possible.
(I still don't know what the formatting tick box does in Add-in set up. As
long as the target cell is formatted to 3 decimal places, it displays
correctly whether the format box in the add in is ticked or not.)

I don't know how you'd query the Yahoo site in a macro to return the
individual pieces of data - Company Name, Last Price, Previous Close etc.
but
that'll be my next mission.

I realised what your code was doing not long after I sent the last post,
but
thanks for the explanation.


"Peter T" wrote:

The code I posted earlier uses the MSN Stock quote addin in the normal
way
of adding a function as a cell formula. It also references the addin's
"dll"
to use the same functions directly in VBA. Both methods give identical
results and that is to be expected.

On reading your OP I assumed when you said you were getting values with
VBA
you meant the same way as I posted, later your code clarified you were
doing
a web query, in effect getting the details from this address

http://moneycentral.msn.com/investor...?SYMBOL=AU:AZZ

All the prices in this link are shown to 2dp with the AZZ ticker, if you
go
to the main moneycentral site you will get same.

As for the 10dp my debug result return, I don't think the additional dp
are
meaningful, I suspect the result of some rounding not ending up as the
intended 2dp.

Looks like prices for your AZZ are sometimes in 0.5 cent (Aus)
increments,
try this in a browser then as a web query:

http://finance.yahoo.com/q?s=AZZ.AX
(or similar local link)

It'd be easy enough to adapt this source to your needs.

Regards,
Peter T