Import data from a secure website to Excel

Discussion:

(too old to reply)

Matheus

2007-09-16 21:34:00 UTC

Hi,
I'm trying to get data imported from our company web system but the web
query doesn't get data from password protected site. Does anyone know a way
arround it? Anything would help. Thanks!

Randy Harmelink

2007-09-16 22:35:02 UTC

Permalink

I use web queries to grab data from password-protected sites all the
time. The key is to login to the web site first. That creates a
login cookie for later queries to use (provided that is how security
is handled on the site). You can go through a manual web query
process, but don't need to actually import anything, but just go
through the login process. In most cases, you can alternatively use
IE to login to the web site first -- that usually creates the login
cookie as well.

For some sites, I've only had to do that once. Other times, I need to
do it once per EXCEL session. It depends on when the cookie expires.

Post by Matheus
I'm trying to get data imported from our company web system but the web
query doesn't get data from password protected site. Does anyone know a way
arround it? Anything would help. Thanks!

Matheus

2007-09-17 03:10:01 UTC

Permalink

Hi Randy,

I tried both ways but it didn't work. Maybe it is because the part I want to
query is a report that needs to be generated through 3 steps?

Here is what happens: I login on the site using the Web Query window, then I
click on the "reports" link. Then, I select the report, select the
organization and then the metrics. When I get the report on the window I
don't see the yello box where I can select the report to be queried.

If I try to open the site using the IE, then follow all the steps above to
get the report and then right click on the report and select "Export to
Excel" I get the following message "This web query returned no data. To
modify the query, click OK, click the name of the external data range in the
name box of the formula bar, and then Edit Query on the External Data
toolbar".

Any ideas?

Post by Randy Harmelink
I use web queries to grab data from password-protected sites all the
time. The key is to login to the web site first. That creates a
login cookie for later queries to use (provided that is how security
is handled on the site). You can go through a manual web query
process, but don't need to actually import anything, but just go
through the login process. In most cases, you can alternatively use
IE to login to the web site first -- that usually creates the login
cookie as well.
For some sites, I've only had to do that once. Other times, I need to
do it once per EXCEL session. It depends on when the cookie expires.

Randy Harmelink

2007-09-17 04:45:42 UTC

Permalink

There are ways to code web pages so that Web Queries won't pick them
up. For example, I saw one government site that didn't want the data
to be picked up that way. Their solution? Code the tables AFTER the
</html> tag. It appears EXCEL ignores all HTML code after that tag --
so it found no tables on the page.

I use an EXCEL add-in for most of my web interaction. I wrote it
because of difficulties and issues I kept having with web queries. I
don't know if it would work for you or not. It did work for the
person that was trying to access the government site above. That's
because my add-in uses the XMLHTTP method to retrieve the source code
of the page instead of going through the IE object. It then parses
the code to extract the data. If you're interested, the add-in,
documentation on its functions, and samples of its usage (mostly for
stock market data) can be found in the files area of this Yahoo Group:

http://finance.groups.yahoo.com/group/smf_addin/

The add-in is free and open-source. If nothing else, you may be able
to use some of the VBA code for your particular needs. I have run
into websites (mostly ASP) from which I can't get data from, but most
work fine. I don't know if an intranet would be easier?

Come to think of it, if this is your own company's web pages, couldn't
they create an export or download option for you? I would think if
you have a need for the data, someone else probably does as well. I
used to work in IT, and I know I used to try and give people such
options when they had the need.

You may also need to know the HTML parameters used to create your
final report page. That would remove the need to interact with the
web pages to create the report you're ready to download it. I don't
know if your intranet processing would make that easy or difficult.

Another option would be to actually interface with the intranet
process through the IE object, then collect the web page data at the
end of that processing. Not a trivial task, especially if your IT
area can do it for you automatically...

Post by Matheus
If I try to open the site using the IE, then follow all the steps above to
get the report and then right click on the report and select "Export to
Excel" I get the following message "This web query returned no data. To
modify the query, click OK, click the name of the external data range in the
name box of the formula bar, and then Edit Query on the External Data
toolbar".
Any ideas?

Matheus

2007-09-17 13:36:01 UTC

Permalink

Randy,
I went to the suggested Yahoo Group, to the Files area and found the Add-In
Files folder. I downloaded the RCH_Stock_Market_Functions.zip in my
C:\Program Files\SMF Add-in. But now I don't know how to use it... Could you
please help? sorry for the stupid question but I'm just a "curious" excel
user and add-ins for me is something new.

Post by Randy Harmelink
There are ways to code web pages so that Web Queries won't pick them
up. For example, I saw one government site that didn't want the data
to be picked up that way. Their solution? Code the tables AFTER the
</html> tag. It appears EXCEL ignores all HTML code after that tag --
so it found no tables on the page.
I use an EXCEL add-in for most of my web interaction. I wrote it
because of difficulties and issues I kept having with web queries. I
don't know if it would work for you or not. It did work for the
person that was trying to access the government site above. That's
because my add-in uses the XMLHTTP method to retrieve the source code
of the page instead of going through the IE object. It then parses
the code to extract the data. If you're interested, the add-in,
documentation on its functions, and samples of its usage (mostly for
http://finance.groups.yahoo.com/group/smf_addin/
The add-in is free and open-source. If nothing else, you may be able
to use some of the VBA code for your particular needs. I have run
into websites (mostly ASP) from which I can't get data from, but most
work fine. I don't know if an intranet would be easier?
Come to think of it, if this is your own company's web pages, couldn't
they create an export or download option for you? I would think if
you have a need for the data, someone else probably does as well. I
used to work in IT, and I know I used to try and give people such
options when they had the need.
You may also need to know the HTML parameters used to create your
final report page. That would remove the need to interact with the
web pages to create the report you're ready to download it. I don't
know if your intranet processing would make that easy or difficult.
Another option would be to actually interface with the intranet
process through the IE object, then collect the web page data at the
end of that processing. Not a trivial task, especially if your IT
area can do it for you automatically...

Randy Harmelink

2007-09-17 17:57:54 UTC

Permalink

You unzipped the files right? Then activated the add-in as described
in the "Overview" document? The add-in basically just gives you a
library of functions and macros that are available to all of your
workbooks. If everything is working OK, you should be able to use
this function in your worksheet:

=RCHGetElementNumber("Version")

...and have it return:

Stock Market Functions add-in, Version 2.0f

Since you are doing a table extraction, the next step would be to use
the RCHGetHTMLTable() function or the RCHGetTableCell() function. The
former can grab the whole table at once. The latter can grab
individual cells. I would suggest the former for now. Look over the
documentation and see if you can get the function working with the
examples given. Then it's just a matter of playing with the
parameters to get it to work with your web page.

Unfortunately, I am going to be at a loss as to tell you how to do
that, because I can't see the specifics of the web page. However, if
you know of a unique string within the table, say from a header of a
column of such, the function can be as simple as:

=RCHGetHTMLTable("...url...","...unique string...",-1,"",1)

But you do need to array-enter it over a range so that the function
can return a range of data. If you don't array-enter it, you just get
the first cell of the table returned.

Post by Matheus
I went to the suggested Yahoo Group, to the Files area and found the Add-In
Files folder. I downloaded the RCH_Stock_Market_Functions.zip in my
C:\Program Files\SMF Add-in. But now I don't know how to use it... Could you
please help? sorry for the stupid question but I'm just a "curious" excel
user and add-ins for me is something new.

Matheus

2007-10-10 20:11:01 UTC

Permalink

Randy, it's been a while since the last post. Now my company changed the
intranet system I was trying to get data from and basically I'll have to
start this discussion again.

On this new version here is what I have to do, that I would really be
interested on excel doing it for me:

1. access the following intranet page:
http://eqos.ssc.trw.com/bess/Default.aspx
2. On this page, click on "Login" link, that takes me to the following page:
https://eqos.ssc.trw.com/BESS/DefaultLoginPage.aspx
3. Then I enter my user name on a box, my password in another box and click
on "Log in"
4. Then, I go back to the http://eqos.ssc.trw.com/bess/Default.aspx page,
but now I see my name on the top of the page indicating that I'm logged in.
5. On this page, I click on a button called "HR" which takes me to this
page: http://eqos.ssc.trw.com/BESS/metrics/MetricSummaryView.aspx
6. On this page I click on a HR Metric called "General Turnover" that takes
me to http://eqos.ssc.trw.com/BESS/metrics/MetricTrendChart.aspx, and on here
I select from a drop down menu the option "By Organization", which takes me
to http://eqos.ssc.trw.com/BESS/metrics/MetricViewByOrganization.aspx
7. Then on this page I click on "Expand All" so I can see the complete list
of sites that we have (around 160). Now I see all the sites' information for
the metric General Turnover. And now this is the table I want to export but I
can't!

The big thing is that I have 13 metrics that I need to do this. That means
that, after I perform steps 1-7 above, I need to change the metric on that
last page I referred by clicking on the metric I want, for example
"Absenteeism", and the table is updated with the absenteeism information for
all the sites.

So now, I kind of need to do steps 1-7 then copy and paste tables 13 times!
Do you now if a way, if there is any, to do this automatically? I've talked
to my IT people and they will not work on a report that I can just download
data from all the metrics at once. HELP!!!

Post by Randy Harmelink
You unzipped the files right? Then activated the add-in as described
in the "Overview" document? The add-in basically just gives you a
library of functions and macros that are available to all of your
workbooks. If everything is working OK, you should be able to use
=RCHGetElementNumber("Version")
Stock Market Functions add-in, Version 2.0f
Since you are doing a table extraction, the next step would be to use
the RCHGetHTMLTable() function or the RCHGetTableCell() function. The
former can grab the whole table at once. The latter can grab
individual cells. I would suggest the former for now. Look over the
documentation and see if you can get the function working with the
examples given. Then it's just a matter of playing with the
parameters to get it to work with your web page.
Unfortunately, I am going to be at a loss as to tell you how to do
that, because I can't see the specifics of the web page. However, if
you know of a unique string within the table, say from a header of a
=RCHGetHTMLTable("...url...","...unique string...",-1,"",1)
But you do need to array-enter it over a range so that the function
can return a range of data. If you don't array-enter it, you just get
the first cell of the table returned.

Randy Harmelink

2007-10-10 20:44:22 UTC

Permalink

It sounds like you'll need to use the IE object to navigate the
website and grab the data. Not something easily explained, especially
when talking about the details -- it requires examination of the web
page structure and the objects it contains. When I learned how to do
it, it was by examining various examples on the Internet that I found
using a search engine like Google. For example:

http://www.google.com/search?q=vba%20InternetExplorer&num=100

For the most part, I stopped doing that after I wrote the add-in. But
you can use the IE object to enter data into text boxes and click on
objects.

Post by Matheus
Randy, it's been a while since the last post. Now my company changed the
intranet system I was trying to get data from and basically I'll have to
start this discussion again.
On this new version here is what I have to do, that I would really be

Matheus

2007-10-10 21:06:26 UTC

Permalink

Does your add-in have the capabilty to do this? I've tried and couldn't get
it to retrive the data I wanted.

Post by Randy Harmelink
It sounds like you'll need to use the IE object to navigate the
website and grab the data. Not something easily explained, especially
when talking about the details -- it requires examination of the web
page structure and the objects it contains. When I learned how to do
it, it was by examining various examples on the Internet that I found
http://www.google.com/search?q=vba%20InternetExplorer&num=100
For the most part, I stopped doing that after I wrote the add-in. But
you can use the IE object to enter data into text boxes and click on
objects.

Randy Harmelink

2007-10-11 02:11:51 UTC

Permalink

My add-in doesn't interact with the IE object. It retrieves the HTML
source code for a given URL and extracts data from the source code.
In order to use my add-in, you need to have a fully qualified URL that
would get you to a web page that contains the data you want, AND it
would need to be able to be retrieved using the XMLHTTP method.

Post by Matheus
Does your add-in have the capabilty to do this? I've tried and couldn't get
it to retrive the data I wanted.