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/...D8-9305-4535-B939-3BF0A740A9B1&displaylang=en
:
Previous Posts In This Thread:
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?
wrote:what add-in?--ron
wrote:
what add-in?
--ron
Look here ...
http://www.microsoft.com/downloads/details.aspx?
Look here ...
http://www.microsoft.com/downloads/...D8-9305-4535-B939-3BF0A740A9B1&displaylang=en
:
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
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.
:
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
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
in message< snip>OK, now I am getting same' cells & VBA30.
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
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?
:
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.
:
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/external/excel/quotes.asp?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
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
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.
:
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/external/excel/quotes.asp?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
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.
:
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/tutorial...b-f54c56a64ed9/get-silverlight-4-install.aspx