If I use the Excel addin to retrieve a stock quote it gives 3 decimal places,
however if I use VBA code to retrieve the quote from MoneyCentral it only
gives two decimal places. This seems to be a limitation of MoneyCentral
rather than the vba code. Where does the addin get its data from?

What code can I use in vba to retrieve 3 decimal places?

On Mon, 11 Jan 2010 18:09:01 -0800, Ken G
wrote:

If I use the Excel addin to retrieve a stock quote it gives 3 decimal places,
however if I use VBA code to retrieve the quote from MoneyCentral it only
gives two decimal places. This seems to be a limitation of MoneyCentral
rather than the vba code. Where does the addin get its data from?

What code can I use in vba to retrieve 3 decimal places?

what add-in?
--ron

Look here ...
http://www.microsoft.com/downloads/d...displaylang=en


"Ron Rosenfeld" wrote:


what add-in?
--ron
.

On Mon, 11 Jan 2010 19:31:01 -0800, Ken G
wrote:

Look here ...
http://www.microsoft.com/downloads/d...displaylang=en


"Ron Rosenfeld" wrote:


what add-in?
--ron
.


Interesting.

I use that add-in, and all my quotes come back with just two significant
decimal places.

The data providers are listed in Help for the add-in.
--ron

Two things - have you allowed for more than 2 decimal places in your
formatting, and secondly, only smaller priced stocks trade in fractions of
cents, so if you have all blue chips for example, you'll only get two decimal
places. Add a couple of penny stocks to your list and see if you get 3
decimals.

"Ron Rosenfeld" wrote:

On Mon, 11 Jan 2010 19:31:01 -0800, Ken G
wrote:

Look here ...
http://www.microsoft.com/downloads/d...displaylang=en


"Ron Rosenfeld" wrote:


what add-in?
--ron
.


Interesting.

I use that add-in, and all my quotes come back with just two significant
decimal places.

The data providers are listed in Help for the add-in.
--ron
.

On Mon, 11 Jan 2010 22:16:01 -0800, Ken G
wrote:

Two things - have you allowed for more than 2 decimal places in your
formatting, and secondly, only smaller priced stocks trade in fractions of
cents, so if you have all blue chips for example, you'll only get two decimal
places. Add a couple of penny stocks to your list and see if you get 3
decimals.

Yes to both questions. And I de-selected the "formatting" option in the MSN
Stock Quote GUI.

I tried several stocks that trade for less than $1 as well as a few foreign
ADR's which also trade that way.

Occasionally there is data well past the 2nd decimal place (e.g.
17.45000076293950, 0.68999999761581) which I assume is just related to the
inability to express some decimal values in binary.

What stocks/values are you seeing?
--ron

I get same results in cells and in VBA with lots of decimals, see debug
results below

Sub MSNSQtest()
' with ref to
' MSNStockQuote, MSN Stock Quote Add-in for XL
Dim bid As Double, ask As Double
Dim sqf As MSNStockQuote.Functions
Const Q As String = """"

ActiveWorkbook.Worksheets.Add

Range("A1") = "MSFT"
Range("B1") = Replace("=MSNStockQuote('MSFT','Bid Price','US')", "'", Q)
Range("C1") = Replace("=MSNStockQuote('MSFT','Ask Price','US')", "'", Q)

Debug.Print Range("B1").Value2, Range("C1").Value2
' 30.0699996948242 30.0900001525879

Set sqf = New MSNStockQuote.Functions
bid = sqf.MSNStockQuote("msft", "Bid", "us")
ask = sqf.MSNStockQuote("msft", "Ask", "us")

Debug.Print bid, ask
' 30.0699996948242 30.0900001525879

End Sub

Curiously, 2 minutes after running the above I got the following prices from
MS's moneycentral site
Bid: 30.29 Ask: 30.30

Seems odd the prices would have changed that much in that time; I ran the
above routine again and got same results. Quite a discrepancy!

Regards,
Peter T

PS - all results above obtained moments before posting here


"Ken G" wrote in message
...
If I use the Excel addin to retrieve a stock quote it gives 3 decimal
places,
however if I use VBA code to retrieve the quote from MoneyCentral it only
gives two decimal places. This seems to be a limitation of MoneyCentral
rather than the vba code. Where does the addin get its data from?

What code can I use in vba to retrieve 3 decimal places?

"Peter T" wrote in message
< snip
Debug.Print bid, ask
' 30.0699996948242 30.0900001525879

Curiously, 2 minutes after running the above I got the following prices
from MS's moneycentral site
Bid: 30.29 Ask: 30.30

Seems odd the prices would have changed that much in that time; I ran the
above routine again and got same results. Quite a discrepancy!


OK, now I'm getting same

' cells & VBA
30.3799991607666 30.3899993896484

on the web site
Bid 30.38
Ask 30.39

Looks like MS is on the up today!

Peter T

Ron, sorry about the late reply, I've been distracted for a few days. I'm
getting Australian stock data from the ASX and I first noticed this on a
stock called Antares Energy Ltd (AU:AZZ). Its currently around the 67c mark
and according the the ASX website (www.asx.com.au) it opened today 15 Jan at
..705 which so far is the day's high, with a current low at .665 and last sale
at .67

I find the wording of the formatting option a bit ambiguous. Does it refer
to your own pre-set sheet/cell format, or the formatting of the price from
the data source?

"Ron Rosenfeld" wrote:

On Mon, 11 Jan 2010 22:16:01 -0800, Ken G
wrote:

Two things - have you allowed for more than 2 decimal places in your
formatting, and secondly, only smaller priced stocks trade in fractions of
cents, so if you have all blue chips for example, you'll only get two decimal
places. Add a couple of penny stocks to your list and see if you get 3
decimals.

Yes to both questions. And I de-selected the "formatting" option in the MSN
Stock Quote GUI.

I tried several stocks that trade for less than $1 as well as a few foreign
ADR's which also trade that way.

Occasionally there is data well past the 2nd decimal place (e.g.
17.45000076293950, 0.68999999761581) which I assume is just related to the
inability to express some decimal values in binary.

What stocks/values are you seeing?
--ron
.

The Money Central web page seems to be locked at 2 decimal places.
I'm not all that sharp on VBA so I have to try to work out how to build in
the "Dim Double" to get my data as double precision. Can you help? The code
I'm using is modified from an old one I found somewhere on the web ....
ozgrid.com I think.
__________________________

Sub Update()

' Macro recorded 9/30/2004 by Atmel PC - modified 12/23/09 by Ken G

Range("A1").Select

' Check for protected sheet

'ActiveSheet.Unprotect Password:="****"

'Get current date
Range("L3").Select
Selection.Copy
Range("H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Application.ScreenUpdating = False

' Local Variables
Dim rngLookUpSym As Range, rngQuerySym As Range,
rngQuerySymCo As Range
Dim rngQuerySymData As Range
Dim qryTableStocks As QueryTable


' Step 1 : Set Data Ranges
Set rngLookUpSym = Worksheets("Holdings").Range("A5")
Set rngQuerySym = Worksheets("Web Query Page").Range("A1")
Set rngQuerySymCo = Worksheets("Web Query Page").Range("A5")
Set rngQuerySymData = Worksheets("Web Query Page").Range("A5").Range("D1")
Set qryTableStocks = ThisWorkbook.Worksheets("Web Query
Page").QueryTables(1)

' Step 2 : Loop through list of stocks and retrieve market data
Do While rngLookUpSym < ""
rngQuerySym = rngLookUpSym
With qryTableStocks
.Connection = _

"URL;http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL="
& "AU:" & rngQuerySym
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
rngLookUpSym.Range("B1") = rngQuerySymCo
rngLookUpSym.Range("G1") = rngQuerySymData.Value
Set rngLookUpSym = rngLookUpSym.Offset(1, 0)
Loop

'Protect Sheet
'ActiveSheet.Protect Password:="****"
'ActiveSheet.EnableSelection = xlUnlockedCells
Range("A1").Select
Application.ScreenUpdating = True

End Sub
___________________________

All I'm retrieving is the Company name and last price.

"Peter T" wrote:

"Peter T" wrote in message
< snip
Debug.Print bid, ask
' 30.0699996948242 30.0900001525879

Curiously, 2 minutes after running the above I got the following prices
from MS's moneycentral site
Bid: 30.29 Ask: 30.30

Seems odd the prices would have changed that much in that time; I ran the
above routine again and got same results. Quite a discrepancy!


OK, now I'm getting same

' cells & VBA
30.3799991607666 30.3899993896484

on the web site
Bid 30.38
Ask 30.39

Looks like MS is on the up today!

Peter T


.

I added a standard "New Web Query" to a new sheet based on this address

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

As you say it only returns 2dp with AU:AZZ, nothing you can do in Excel to
improve that, Dim'ing with double won't help. Maybe you can find a different
site that will give you more. I understand Yahoo Finance is pretty good with
web queries.

All I'm retrieving is the Company name and last price.

That's because you are only getting name and last-price from the query
sheet,
rngLookUpSym.Range("B1") = rngQuerySymCo
rngLookUpSym.Range("G1") = rngQuerySymData.Value

Look at how you set rngQuerySymData, looks like D5, I guess(?) you'll see
Previous close in E5

Regards,
Peter T



"Ken G" wrote in message
...
The Money Central web page seems to be locked at 2 decimal places.
I'm not all that sharp on VBA so I have to try to work out how to build in
the "Dim Double" to get my data as double precision. Can you help? The
code
I'm using is modified from an old one I found somewhere on the web ....
ozgrid.com I think.
__________________________

Sub Update()

' Macro recorded 9/30/2004 by Atmel PC - modified 12/23/09 by Ken G

Range("A1").Select

' Check for protected sheet

'ActiveSheet.Unprotect Password:="****"

'Get current date
Range("L3").Select
Selection.Copy
Range("H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

Application.ScreenUpdating = False

' Local Variables
Dim rngLookUpSym As Range, rngQuerySym As Range,
rngQuerySymCo As Range
Dim rngQuerySymData As Range
Dim qryTableStocks As QueryTable


' Step 1 : Set Data Ranges
Set rngLookUpSym = Worksheets("Holdings").Range("A5")
Set rngQuerySym = Worksheets("Web Query Page").Range("A1")
Set rngQuerySymCo = Worksheets("Web Query Page").Range("A5")
Set rngQuerySymData = Worksheets("Web Query
Page").Range("A5").Range("D1")
Set qryTableStocks = ThisWorkbook.Worksheets("Web Query
Page").QueryTables(1)

' Step 2 : Loop through list of stocks and retrieve market data
Do While rngLookUpSym < ""
rngQuerySym = rngLookUpSym
With qryTableStocks
.Connection = _

"URL;http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL="
& "AU:" & rngQuerySym
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingAll
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
rngLookUpSym.Range("B1") = rngQuerySymCo
rngLookUpSym.Range("G1") = rngQuerySymData.Value
Set rngLookUpSym = rngLookUpSym.Offset(1, 0)
Loop

'Protect Sheet
'ActiveSheet.Protect Password:="****"
'ActiveSheet.EnableSelection = xlUnlockedCells
Range("A1").Select
Application.ScreenUpdating = True

End Sub
___________________________

All I'm retrieving is the Company name and last price.

"Peter T" wrote:

"Peter T" wrote in message
< snip
Debug.Print bid, ask
' 30.0699996948242 30.0900001525879

Curiously, 2 minutes after running the above I got the following prices
from MS's moneycentral site
Bid: 30.29 Ask: 30.30

Seems odd the prices would have changed that much in that time; I ran
the
above routine again and got same results. Quite a discrepancy!


OK, now I'm getting same

' cells & VBA
30.3799991607666 30.3899993896484

on the web site
Bid 30.38
Ask 30.39

Looks like MS is on the up today!

Peter T


.

On Thu, 14 Jan 2010 19:39:01 -0800, Ken G
wrote:

Ron, sorry about the late reply, I've been distracted for a few days. I'm
getting Australian stock data from the ASX and I first noticed this on a
stock called Antares Energy Ltd (AU:AZZ). Its currently around the 67c mark
and according the the ASX website (www.asx.com.au) it opened today 15 Jan at
.705 which so far is the day's high, with a current low at .665 and last sale
at .67

OK, now I see what you mean. And I agree. I did get .705 for the high, also.

As I wrote, MSNStockQuote data sources are revealed in the Help screen.


I find the wording of the formatting option a bit ambiguous. Does it refer
to your own pre-set sheet/cell format, or the formatting of the price from
the data source?

I agree that it is ambiguous. And I don't know the answer.
--ron

In one of your earlier posts in this thread you had some code that retrieved
about 10 decimal places in a debugging routine. How does that work?
I should have made it clearer that I'm only retrieving the Company Name and
the Last Price because that was part of the modification I did to the
original code to suit what I needed for the particular application.

I still think its odd that if I use the add-in, remove the option to retain
formatting, and format my target cell to 3 decimal places, that it will
correctly retrieve 3 decimal places, presumably from the same MoneyCentral
source as is used in the code.

"Peter T" wrote:

I added a standard "New Web Query" to a new sheet based on this address

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

As you say it only returns 2dp with AU:AZZ, nothing you can do in Excel to
improve that, Dim'ing with double won't help. Maybe you can find a different
site that will give you more. I understand Yahoo Finance is pretty good with
web queries.

All I'm retrieving is the Company name and last price.

That's because you are only getting name and last-price from the query
sheet,
rngLookUpSym.Range("B1") = rngQuerySymCo
rngLookUpSym.Range("G1") = rngQuerySymData.Value

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





"Ken G" wrote in message
...
In one of your earlier posts in this thread you had some code that
retrieved
about 10 decimal places in a debugging routine. How does that work?
I should have made it clearer that I'm only retrieving the Company Name
and
the Last Price because that was part of the modification I did to the
original code to suit what I needed for the particular application.

I still think its odd that if I use the add-in, remove the option to
retain
formatting, and format my target cell to 3 decimal places, that it will
correctly retrieve 3 decimal places, presumably from the same MoneyCentral
source as is used in the code.

"Peter T" wrote:

I added a standard "New Web Query" to a new sheet based on this address

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

As you say it only returns 2dp with AU:AZZ, nothing you can do in Excel
to
improve that, Dim'ing with double won't help. Maybe you can find a
different
site that will give you more. I understand Yahoo Finance is pretty good
with
web queries.

All I'm retrieving is the Company name and last price.

That's because you are only getting name and last-price from the query
sheet,
rngLookUpSym.Range("B1") = rngQuerySymCo
rngLookUpSym.Range("G1") = rngQuerySymData.Value

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

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

Does anyone have the actual add-in file? I am told it should have an .xla extension. Seems that microsoft has chosen to remove the link. The reference site no longer is valid. I am trying to get Microsoft to address this, without success so far.

I used this preiously in Excel 2007, but had a disk problem and when tried to reintall again, cannot find the link to download the file.



Ken G wrote:

Look here ...http://www.microsoft.com/downloads/details.aspx?
11-Jan-10

Look here ...
http://www.microsoft.com/downloads/d...displaylang=en


"Ron Rosenfeld" wrote:

Previous Posts In This Thread:

On Monday, January 11, 2010 9:09 PM
Ken G wrote:

Stock Quotes
If I use the Excel addin to retrieve a stock quote it gives 3 decimal places,
however if I use VBA code to retrieve the quote from MoneyCentral it only
gives two decimal places. This seems to be a limitation of MoneyCentral
rather than the vba code. Where does the addin get its data from?

What code can I use in vba to retrieve 3 decimal places?

On Monday, January 11, 2010 9:30 PM
Ron Rosenfeld wrote:

wrote:what add-in?--ron
wrote:


what add-in?
--ron

On Monday, January 11, 2010 10:31 PM
Ken G wrote:

Look here ...http://www.microsoft.com/downloads/details.aspx?
Look here ...
http://www.microsoft.com/downloads/d...displaylang=en


"Ron Rosenfeld" wrote:

On Monday, January 11, 2010 11:09 PM
Ron Rosenfeld wrote:

wrote:Interesting.
wrote:


Interesting.

I use that add-in, and all my quotes come back with just two significant
decimal places.

The data providers are listed in Help for the add-in.
--ron

On Tuesday, January 12, 2010 1:16 AM
Ken G wrote:

Two things - have you allowed for more than 2 decimal places in
Two things - have you allowed for more than 2 decimal places in your
formatting, and secondly, only smaller priced stocks trade in fractions of
cents, so if you have all blue chips for example, you will only get two decimal
places. Add a couple of penny stocks to your list and see if you get 3
decimals.

"Ron Rosenfeld" wrote:

On Tuesday, January 12, 2010 6:38 AM
Ron Rosenfeld wrote:

wrote:Yes to both questions.
wrote:


Yes to both questions. And I de-selected the "formatting" option in the MSN
Stock Quote GUI.

I tried several stocks that trade for less than $1 as well as a few foreign
ADR's which also trade that way.

Occasionally there is data well past the 2nd decimal place (e.g.
17.45000076293950, 0.68999999761581) which I assume is just related to the
inability to express some decimal values in binary.

What stocks/values are you seeing?
--ron

On Tuesday, January 12, 2010 10:50 AM
Peter T wrote:

I get same results in cells and in VBA with lots of decimals, see debugresults
I get same results in cells and in VBA with lots of decimals, see debug
results below

Sub MSNSQtest()
' with ref to
' MSNStockQuote, MSN Stock Quote Add-in for XL
Dim bid As Double, ask As Double
Dim sqf As MSNStockQuote.Functions
Const Q As String = """"

ActiveWorkbook.Worksheets.Add

Range("A1") = "MSFT"
Range("B1") = Replace("=MSNStockQuote('MSFT','Bid Price','US')", "'", Q)
Range("C1") = Replace("=MSNStockQuote('MSFT','Ask Price','US')", "'", Q)

Debug.Print Range("B1").Value2, Range("C1").Value2
' 30.0699996948242 30.0900001525879

Set sqf = New MSNStockQuote.Functions
bid = sqf.MSNStockQuote("msft", "Bid", "us")
ask = sqf.MSNStockQuote("msft", "Ask", "us")

Debug.Print bid, ask
' 30.0699996948242 30.0900001525879

End Sub

Curiously, 2 minutes after running the above I got the following prices from
MS's moneycentral site
Bid: 30.29 Ask: 30.30

Seems odd the prices would have changed that much in that time; I ran the
above routine again and got same results. Quite a discrepancy!

Regards,
Peter T

PS - all results above obtained moments before posting here

On Tuesday, January 12, 2010 11:13 AM
Peter T wrote:

"Peter T" wrote in message< snipOK, now I am getting same' cells & VBA30.
"Peter T" wrote in message
< snip


OK, now I am getting same

' cells & VBA
30.3799991607666 30.3899993896484

on the web site
Bid 30.38
Ask 30.39

Looks like MS is on the up today!

Peter T

On Thursday, January 14, 2010 10:39 PM
Ken G wrote:

Ron, sorry about the late reply, I have been distracted for a few days.
Ron, sorry about the late reply, I have been distracted for a few days. I am
getting Australian stock data from the ASX and I first noticed this on a
stock called Antares Energy Ltd (AU:AZZ). Its currently around the 67c mark
and according the the ASX website (www.asx.com.au) it opened today 15 Jan at
..705 which so far is the day's high, with a current low at .665 and last sale
at .67

I find the wording of the formatting option a bit ambiguous. Does it refer
to your own pre-set sheet/cell format, or the formatting of the price from
the data source?

"Ron Rosenfeld" wrote:

On Thursday, January 14, 2010 11:13 PM
Ken G wrote:

The Money Central web page seems to be locked at 2 decimal places.
The Money Central web page seems to be locked at 2 decimal places.
I am not all that sharp on VBA so I have to try to work out how to build in
the "Dim Double" to get my data as double precision. Can you help? The code
I am using is modified from an old one I found somewhere on the web ....
ozgrid.com I think.
__________________________

Sub Update()

' Macro recorded 9/30/2004 by Atmel PC - modified 12/23/09 by Ken G

Range("A1").Select

' Check for protected sheet

'ActiveSheet.Unprotect Password:="****"

'Get current date
Range("L3").Select
Selection.Copy
Range("H3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _

Application.ScreenUpdating = False

' Local Variables
Dim rngLookUpSym As Range, rngQuerySym As Range,
rngQuerySymCo As Range
Dim rngQuerySymData As Range
Dim qryTableStocks As QueryTable


' Step 1 : Set Data Ranges
Set rngLookUpSym = Worksheets("Holdings").Range("A5")
Set rngQuerySym = Worksheets("Web Query Page").Range("A1")
Set rngQuerySymCo = Worksheets("Web Query Page").Range("A5")
Set rngQuerySymData = Worksheets("Web Query Page").Range("A5").Range("D1")
Set qryTableStocks = ThisWorkbook.Worksheets("Web Query
Page").QueryTables(1)

' Step 2 : Loop through list of stocks and retrieve market data
Do While rngLookUpSym < ""
rngQuerySym = rngLookUpSym
With qryTableStocks
..Connection = _

"URL;http://moneycentral.msn.com/investor/external/excel/quotes.asp?SYMBOL="
& "AU:" & rngQuerySym
..WebSelectionType = xlEntirePage
..WebFormatting = xlWebFormattingAll
..WebPreFormattedTextToColumns = True
..WebConsecutiveDelimitersAsOne = True
..WebSingleBlockTextImport = False
..WebDisableDateRecognition = False
..Refresh BackgroundQuery:=False
End With
rngLookUpSym.Range("B1") = rngQuerySymCo
rngLookUpSym.Range("G1") = rngQuerySymData.Value
Set rngLookUpSym = rngLookUpSym.Offset(1, 0)
Loop

'Protect Sheet
'ActiveSheet.Protect Password:="****"
'ActiveSheet.EnableSelection = xlUnlockedCells
Range("A1").Select
Application.ScreenUpdating = True

End Sub
___________________________

All I am retrieving is the Company name and last price.

"Peter T" wrote:

On Friday, January 15, 2010 8:23 AM
Peter T wrote:

I added a standard "New Web Query" to a new sheet based on this
I added a standard "New Web Query" to a new sheet based on this address

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

As you say it only returns 2dp with AU:AZZ, nothing you can do in Excel to
improve that, Dim'ing with double will not help. Maybe you can find a different
site that will give you more. I understand Yahoo Finance is pretty good with
web queries.


That's because you are only getting name and last-price from the query
sheet,
rngLookUpSym.Range("B1") = rngQuerySymCo
rngLookUpSym.Range("G1") = rngQuerySymData.Value

Look at how you set rngQuerySymData, looks like D5, I guess(?) you will see
Previous close in E5

Regards,
Peter T

On Friday, January 15, 2010 8:51 AM
Ron Rosenfeld wrote:

wrote:OK, now I see what you mean. And I agree. I did get .
wrote:


OK, now I see what you mean. And I agree. I did get .705 for the high, also.

As I wrote, MSNStockQuote data sources are revealed in the Help screen.


I agree that it is ambiguous. And I do not know the answer.
--ron

On Friday, January 15, 2010 8:56 PM
Ken G wrote:

In one of your earlier posts in this thread you had some code that
In one of your earlier posts in this thread you had some code that retrieved
about 10 decimal places in a debugging routine. How does that work?
I should have made it clearer that I am only retrieving the Company Name and
the Last Price because that was part of the modification I did to the
original code to suit what I needed for the particular application.

I still think its odd that if I use the add-in, remove the option to retain
formatting, and format my target cell to 3 decimal places, that it will
correctly retrieve 3 decimal places, presumably from the same MoneyCentral
source as is used in the code.

"Peter T" wrote:

On Saturday, January 16, 2010 9:10 AM
Peter T wrote:

The code I posted earlier uses the MSN Stock quote addin in the normal wayof
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 do not 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

On Thursday, January 21, 2010 7:30 AM
Ken G wrote:

Hello again.Seems to be some confusion re my original post.
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 is not possible.
(I still do not 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 do not know how you would query the Yahoo site in a macro to return the
individual pieces of data - Company Name, Last Price, Previous Close etc. but
that will 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:

On Thursday, January 21, 2010 10:06 AM
Peter T wrote:

I had not seen MSNStockQuote give 3dp but if you have seen it clearly it does.
I had not seen MSNStockQuote give 3dp but if you have 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 is nothing
you can do to get more. it is 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 will not want all the details returned as in the above,
rewrite the last loop to your needs, and add the appropriate labels.

Regards,
Peter T


Submitted via EggHeadCafe - Software Developer Portal of Choice
Get Silverlight 4 Installed: Tips and Tricks
http://www.eggheadcafe.com/tutorials...4-install.aspx

On Monday, 11 January 2010 18:09:01 UTC-8, Ken G wrote:
If I use the Excel addin to retrieve a stock quote it gives 3 decimal places,
however if I use VBA code to retrieve the quote from MoneyCentral it only
gives two decimal places. This seems to be a limitation of MoneyCentral
rather than the vba code. Where does the addin get its data from?

What code can I use in vba to retrieve 3 decimal places?