Free Microsoft Excel 2013 Quick Reference

Web query against MSN Money

I posted the below to various technical support groups. Anyone have any ideas?

________________________________________________________

I have been passed across multiple technical support groups, with each
stating that the problem is in another area. This email is being sent to the
Office, MSN, and Money technical support groups. Surely one of you should own
the problem.

The problem is with the MSN Money website. I have used Excel web queries for
years to download financial data. In the last couple of weeks it has stopped
working BECAUSE A CHANGE HAS BEEN MADE TO THE WEBSITE. I am trying to cut my
losses at this point and determine if it is temporary, and if anyone is
working on it, or if MSN has decided to not allow download of financial data.

The simplest description of the problem is as follows:

1. Using a new Excel workbook with no data.
2. Use the Data/Import External Data/New Web Query menu selection.
3. Use the browser window to browse to the data, such as the basic quotes
table in MSN, and select the table.
4. Select Import.

The following message dialog is displayed:

Unable to open http://moneycentral.msn.com/detail/s...ote?Symbol=aig.
The Internet site reports that a connection was established but the data is
not available.

This occurs every time an import is attempted, and has been occuring for
about the last two or three weeks.

The same query works fine against Yahoo or Morningstar, so the problem is
with the MSN Money website.


Post your answer or comment

comments powered by Disqus
Quite simply - does anyone know how Excel assigns numbers to tables used to import via Web Query. I am trying to code navigating www.edmunds.com to select a used vehicle, then click on the compare vehicles, then perform a web query on the comparison table (I made a related post the other day re:how to import the pictures).

Because edmunds sponsors ads related to the car chosen, the table I want to perform the web query against is not the same number each time

I assumed it would be part of the numbered index for GetElementsByTagName("TABLE") which I can iterate from the Url's HTML. But I tried to compare [with a Honda Civic], and Excel said the table was 72, which was equal to GetElementsByTagName("TABLE").Item(47); although GetElementsByTagName("TABLE").Item(72) was the First Cell in the table.

I assume there is some logic to this that I don't understand, and any help would be appreciated.

TIA

Stan

I have the following code, that I am stuck with, wondered if anyone could help.

I have a series of Web addresses in column K, each of which I want to run a Web Query against, and pull back some data, before moving onto the next cell to get the next address to query that.

I think I have the loop for stepping through sorted, but seem to fall over on the Web Query part, any help would be welcomed.

Code:
Sub GetData()

    Dim MyUrl As String

    Sheets("Sheet1").Select
    ' Find the last row of data
    FinalRow = Range("K65536").End(xlUp).Row
    ' Loop through each row
    For x = 3 To FinalRow
    If x = FinalRow Then End
    
    Range("k" & x).Select
    
    ' Set My URL to be the value of The Selected Cell.
    MyUrl = Range("K" & x).Value
    
    ' Send WebQuery based on Valu of MyURL, and place result one cell to right
With ActiveSheet.QueryTables.Add(Connection:="URL;" & MyUrl, Destination:=Cells.Offset(0, 1))
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "5"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
End With

    Next x
End Sub


Hi

I would appreciate any help on the following issue I'm having.

I need to pull data from an internal company phonebook and parse that data in Excel. Unfortunately I can't provide any links as they wouldn't work anyway. I will be including the .igy and workbook to show what I've been able to accomplish thus far.

What I would like happen:
1. Add list of ID #s to spreadsheet and automatically run a web query against the web phonebook and pull the data.
2. Sort this data from the current single column (multiple rows) to a single row (multiple columns)
3. Append new data to next row, not overwrite the first row.
4. Automatically have all of the above done by running a macro or VBA.

My apologies if I'm not explaining this properly.

What I have done:
1. Add list of ID #s to spreadsheet (Column A) but can only run one query (phonebook2.txt, renamed from phonebook2.igy) at a time which posts the data to column B.
2. Used an INDEX formula to sort data from column B to Column C, D, E, etc...
=INDEX($B:$B, COLUMN()+((ROW()-2)*8))

Attached are the files, any help is appreciated!

Hi All
I use web queries to download stock options from MSN Money.
If I manually refresh the queries (right click -refresh) the downloaded
prices are in number format.

If I use:
Sub Query_All ()  
    ActiveWorkbook.RefreshAll  
    ActiveWorkbook.Save
End Sub
the numbers are in text format. I need the numbers in number
format but like the simple way of Query_All to do a refresh.
How do I achieve this?
Would also like to run this macro in background.

Thanks
Al

Currently, I can manually download historical stock data from MSN Money in an
Excel spreadsheet by first displaying the chart for the stock, and then
selecting "File - Export Data". Previously, I was able to achieve this via
an Excel VBA macro using the following web query URL:

http://moneycentral.msn.com/scripts/...&FileDownload=

However, MSN had changed the URL during one of their site upgrades and the
above URL no longer works (and I suspect some or all of the parameters have
changed as well). Does anyone know what the new URL is? Basically, I want
to be able to download historical stock data via an Excel VBA macro as if I
were doing it manually via the site's chart module (File - Export Data).

Thanks,
-Alex

Hello all,
I'm currently developing an excel sheet to make commercial calculations, and one of the first "goodies" I'd like to have is a dynamic currency table.

I found one that fits my informative needs (http://moneycentral.msn.com/investor...on=0&Compare=9) or even the smaller (http://money.cnn.com/data/currencies/). The problem I have is that when I create a web query to get those small tables, some of the numbers don’t have the correct formatting.
For example:
In the site, 1.4707 goes into excel like 14707 so it is recognized as 14,707. However, values smaller than 1 go ok, like 0.5318 goes correctly as 0.5318.

What am I doing wrong?

PS: keep in mind that I am in Europe, so the decimal symbol is a “,” (comma) instead of the “.” (dot).

is it possible to put a delay before a web query executes?

i have an excel spreadsheet that has about 8 tabs in it. when you change the value in a particular cell of the first tab, the other 7 will recognize it and update the data for the new value.

the problem is that once the queries begin to refresh, i get an "invalid web query" error. if i press ok and then refresh the queries, it will fill in more data, and then give another error. after about 2 or 3 of the same errors and retrying, it will eventually populate the page fully and give no errors.

i believe this has something to do with the queries are running a bit faster than the web pages load. is it possible to put a delay within the query before it executes?

edit: by the way, this is for fundamental analysis on stocks. enter a symbol and the fields all populate automatically. i have already integrated an excel spreadsheet to a paysite (investools), and it works fine. now i am trying to make one that runs off of msn money's site, and its giving me trouble.

Hi-
I am using the MSN Money macro and I currently have it get the range of
symbols to lookup from a range of cells on a worksheet. My problem is
that when I enter a new symbol at the bottom of the range I have to go
in and manually update the query everytime to expand the selection box
by one cell. I cannot simply select the whole column however as there
text above and below that I do not want to import. Is there anyway to
change the web query parameters via a macro? Thank you,
-Dan

I am using the External Web Query to retrieve data from several financial
web sites. MSN Money and Yahoo are the most common sites visited.

When using "Refresh All" from the External Data Toolbar, Excel frequently
returns an “Invalid Web Query” error. When the query is edited, many times
there seems to be a disconnect from where the data was originally requested.
The query returns to the correct web page but there is no check next to the
data originally requested or the check has moved to another data area.

As best as I can tell, this error appears to be common when the query looks
at a Yahoo Finance page. I do not seem to have the same issues with MSN
Money’s web page(s)

Any thoughts or insights would be greatly appreciated.

Yellowbird

--
SHD

I'm using a macro to run a web query to pull financial stmts off of MSN. I enter the stock ticker in a cell and hit a button to run a macro to pull in the information. Query works great. Problem is that every time you change the ticker and run the macro again, Excel saves a new named range. I put in some code to delete the query and the named range, but when I run the query again, Excel saves the named range again with and increment integer added on. If you drop the file and bring it back up, it reinitializes the count, but still does the same thing. I figure that there are two avenues to solve my problem, but don't know the answer to either.

1. I don't delete the query, but I would then need a way to modify the query with a new stock ticker. Problem is, I can't figure out where Excel hold the query and how I would modify it. This solution would just refresh the query, and not add a new one to the file like my code below.

2. The second path would be to figure out how to get Excel to quit incrementing the named ranges.

Can anyone help me out?

Thanks

Here is the code:

Sub DownloadWebData()

Application.ScreenUpdating = False

Dim Ticker As String

Ticker = Range("A2:A2").Value

Range("A5:IV65536").Select
Selection.ClearContents
Range("A1").Select

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Download Annual Inc Stmt
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://moneycentral.msn.com/investor/invsub/results/statemnt.asp?lstStatement=Income&Symbol=" & Ticker & "&stmtView=Ann" _
, Destination:=Range("A5"))
.Name = "zss"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "16"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This is where I delete the query and the named range

Sheets("Download").QueryTables("zss").Delete
ActiveWorkbook.Names("zss").Delete
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Range("A1:A1").Select

Application.ScreenUpdating = True

End Sub

I am trying to run multiple web queries in excel vba and I get the 1004 - Application-defined or object-defined error when the code reaches the second ".Refresh" line (see code below). Basically, if I run the code below using something like www.msn.com as the url, then it works just fine, but if I use www.yahoo.com, then it errors out on the ".Refresh" in the second With block. Can someone please help?

Sheets("Sheet1").Select
Cells(1, 1).Select

varconnection = "http://www.yahoo.com"

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & varconnection, Destination:=Range( _
"A1"))
.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With

I run a web query from http://moneycentral.msn.com/investor...xcel/rates.asp to import the latest currency rates into Excel. I refresh the data every minute. Every time Cell B19 changes I want to run the following code:

	VB:
	
 
    MsgBox "buy" 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I tried putting that code in the Worksheet_Change event but it only works if the user physically changes the cell, not if it's changed by a web query. So how can I run code that triggers when a cell is changed by a web query?

I'm running an excel sheet with a series of web queries. They seem to work
for most people, but a couple of people get the following message:

"Unable to open
http://moneycentral.msn.com/investor...sp?SYMBOL=MSFT.
The internet site reports that a connection was established but the data is
unavailable."

It seems to happen regardless of what website I'm using.

Does anybody have any reason why this works for some people and not others?
Is it a setting within Windows, Excel, or Internet Explorer?

I need your help on Web Query function of Excel.

I have edited Import data macro “MSN Moneycentral Investor Quote.iqy”
file to fetch a value from a web page I have pointed and I got the
value I wanted. However, I have encountered a problem.

MSN Stock quote macro accesses the web page that supports multiple
input parameters and returns multiple values on one page, where the web
page I am working on returns one value per page.

In order for this macro to return multiple value I guess I have to use
some form of nested statement for the Web query and I do not know how
to do that.

Can you help please?

--
QLS_KIM

I have a spreadsheet that does a Data/Import External Data/Web Query to get
data from a table on a web page. It has worked for years. On January 31,
a change was made to the web page and my query stopped working with the
following message: "Unable to open http://finance.yahoo.com/p?v&k=pf_1. ;
The
internet site reports that a connection was established but the data is not
available." (The actual url varies. This happens with multiple pages while
wotking with other pages from the same provider.) I have redone the query
with the same results. I tried MSN and also got the same results.
Yahoo'sresponse was they they don't know what the problem is but it isn't
anything at their end so they can't/won't do anything. I really rely on this
feature and it's absence is hurting. Anyone got any ideas? I have also
discovered through posts like this, that I am not the only one getting this
error on various pages. Since i know the web page changeed, does anyone know
what one does to a web page that produces this result? Carl
(Running current XP and Office 2003)

I have been using the "Microsoft Investor Stock Quotes.iqy" that was included
in Excel 2000 to get current stock prices. On July 7, 2000, this query
stopped working, returning the following error message: "Unable to open
http://investor.msn.com/external/exc...p?SYMBOL=x+y+z
Cannot download the information you requested."

Has Microsoft stopped supporting this web query?

Are there any workarounds? I have 6 years of portfolio performance data in
an excel spreadsheet that I would like to keep using.

Thanks.

I have a couple of questions about Web querying with Excel. First, is it
possible to create queries for Stock Scouter rankings on a column of stock
symbols consisting of, say, 100 stock symbols?

The link to Microsoft's basic html link to obtain a Stock Scouter rank is
shown below, with the symbol for Microsoft (symbol=MSFT) as shown at the end
of the link:

http://moneycentral.msn.com/investor...sp?Symbol=msft

So, I'm hoping to use the above link for a single column of about 100
symbols, which will change weekly, on a spreadsheet, with the accompanying
Stock Scouter score queried and displayed in an accompanying column. The
two confusing (for me) problems I have with this objective a

1) The symbols for the single column will change weekly, and it would be
useful if Excel could accomodate the change.
2) I don't quite understand how to use the HTML link above in context of the
changing symbols and what gets displayed in each cell. All I'm interested
in obtaining is the numeric score for each symbol without any additional
information from the page.

If this is possible to do with Excel, can instructions be posted for a
technophobe on how to do this?

Thank you for any assistance.

I have a web query in VBA that is not working although it works in other
computers. The reason is that it does not access the Internet. The same
happens if I don't use VBA. After the usual Data/Import External Data/New
Web Query the window starts in
www.google.com and displays

"Action Canceled. Internet Explorer was unable to link to the Web page
you requested. The page might be temporarily unavailable."

If I enter

http://moneycentral.msn.com/detail/s...ote?Symbol=aig
or
http://moneycentral.msn.com/investor...asp?SYMBOL=IBM

I receive a nasty

"The page cannot be displayed. The page you are looking for is currently
unavailable. The Web site might be experiencing technical difficulties, or
you may need to adjust your browser settinngs."

However, the same query works in IE. Needless to say, I tried with other
sites, and the result is the same.

I'm using Windows XP, Excel 2003. My computer is part of a LAN, inside a SBC
Yahoo firewall. I also disabled both the Windows and the SBC Yahoo firewalls
and the results are the same: cannot connect.

Any suggestion? Do I have to change any settings? Any Office Control to
download or Office Web Components missing? How to add them?

I looked for the requirements/options/settings to make Web Query run in
Excel 2003 but couldn't find them on the Microsoft website (or anywhere
else).

Thanks guys,

CW

I'm running an excel sheet with a series of web queries. They seem to work
for most people, but a couple of people get the following message:

"Unable to open
http://moneycentral.msn.com/investor...sp?SYMBOL=MSFT.
The internet site reports that a connection was established but the data is
unavailable."

It seems to happen regardless of what website I'm using.

Does anybody have any reason why this works for some people and not others?
Is it a setting within Windows, Excel, or Internet Explorer?

I have a spreadsheet that does a Data/Import External Data/Web Query to get
data from a table on a web page. It has worked for years. On January 31,
a change was made to the web page and my query stopped working with the
following message: "Unable to open http://finance.yahoo.com/p?v&k=pf_1. ;
The
internet site reports that a connection was established but the data is not
available." (The actual url varies. This happens with multiple pages while
wotking with other pages from the same provider.) I have redone the query
with the same results. I tried MSN and also got the same results.
Yahoo'sresponse was they they don't know what the problem is but it isn't
anything at their end so they can't/won't do anything. I really rely on this
feature and it's absence is hurting. Anyone got any ideas? I have also
discovered through posts like this, that I am not the only one getting this
error on various pages. Since i know the web page changeed, does anyone know
what one does to a web page that produces this result? Carl
(Running current XP and Office 2003)

I have been using the "Microsoft Investor Stock Quotes.iqy" that was included
in Excel 2000 to get current stock prices. On July 7, 2000, this query
stopped working, returning the following error message: "Unable to open
http://investor.msn.com/external/exc...p?SYMBOL=x+y+z
Cannot download the information you requested."

Has Microsoft stopped supporting this web query?

Are there any workarounds? I have 6 years of portfolio performance data in
an excel spreadsheet that I would like to keep using.

Thanks.

Hi All.

After the usual Data/Import External Data/New Web Query the window starts in
www.google.com and displays

"Action Canceled. Internet Explorer was unable to link to the Web page
you requested. The page might be temporarily unavailable."

If I enter

http://moneycentral.msn.com/detail/s...ote?Symbol=aig
or
http://moneycentral.msn.com/investor...asp?SYMBOL=IBM

I receive a nasty

"The page cannot be displayed. The page you are looking for is currently
unavailable. The Web site might be experiencing technical difficulties, or
you may need to adjust your browser settinngs."

However, the same query works in IE. Needless to say, I tried with other
sites, and the result is the same.

I'm using Windows XP, Excel 2003. My computer is part of a LAN, inside a SBC
Yahoo firewall. I also disabled both the Windows and the SBC Yahoo firewalls
and the results are the same: cannot connect.

Any suggestion? Do I have to change any settings? Any Office Control to
download or Office Web Components missing? How to add them?

I looked for the requirements/options/settings to make Web Query run in
Excel 2003 but couldn't find them on the Microsoft website (or anywhere
else).

Thanks guys,

CW

I have a spreadsheet that does a Data/Import External Data/Web Query to get
data from my Yahoo stock portfolio. It has worked for years. On January 31,
Yahoo made a change to their web page and my query stopped working with the
following message: "Unable to open http://finance.yahoo.com/p?v&k=pf_1. The
internet site reports that a connection was established but the data is not
available." I have redone the query with the same results. Interestingly, I
can query other tables on Yahoo, just nothing on their finance page. I tried
MSN and also got the same results from a stock portfolio I set up there.
Yahoo fianance's response was they they don't know what the problem is but it
isn't anything at their end so they can't/won't do anything. I really rely
on this feature and it's absence is hurting. Anyone got any ideas? Carl
(Running current XP and Office 2003)

Up until yesterday, my web query in my Excel worksheet has been working
perfectly. I have been using the saved query named "Microsoft Investor
Stock Quotes.iqy". It looks like the server accessed by the URL has been
down. When I ping the domain, I get the message that the ping timed out.
The link used by the query is
http://investor.msn.com/external/exc...?SYMBOL=AA,fdx... Is
anyone else experiencing the same problem? Does anyone know how to contact
MSN to jog them to get things up and running? Or, have they stopped
supporting the query?

Gary


No luck finding an answer? You could always try Google.