Automating a web query login to download data

Excel Help for Automating A Web Query Login To Download Data in Automating Data Analysis Tasks

Find Excel Solutions


Search Forums:
Recent Activity:
Importing Data From Pdf Files
Same Output By Varying The Target Returns
Web Query
Vba Save To Skydrive
Auto-timing Not Working Properly
"runtime Error 13" And "runtime Error -2147483640" Excel 2011 Mac




Follow Business Spreadsheets On:
 
Forum TopicReply Login

Automating A Web Query Login To Download Data

Rate this:
(2.8/5 from 16 votes)
Confused I have a funamental earnings and valuation model spreadsheet that downloads stock data from a number of different websites via certain add-ins.  One of the sites that it gets information from is investors.com, which requires a login before downloading stock information (not just price/volume/etc data, I get that easily from Yahoo).  Problem is that you have to manually login through a web query each time, and this is the current instructions:
  1. Go to "Data" on the Menu bar, then "Import External Data"
  2. Then go to "New Web Query" and a web browser will open
  3. Navigate to www.investors.com and login clicking remember me
  4. Then exit from the query, now you can use this tab
My question is how can I automate the login via either a macro or VBA code to login based upon a user name in one cell (C1), and a password in another cell (C2)?

Thanks in advance for any help you can provide! 
 Posted by on
 
Replies - Displaying 1 to 10 of 40Order Replies By: Most Recent | Chronological | Highest Rated
Happy
Rate this:
(2.7/5 from 15 votes)
You can post form variables to a web query in order to automatically login to a page and retrieve its data.  You need to know the name of the form variables required by the page.  This information can be obtained by right cliking of the page that requires the information and chossing to "View Source".  The names of the form variables that are required to be posted are the values of the "name" attributes for the elements within the "form" tag.

The following VBA code shows how to login into this forum and access this post:

VBA Code:
Sub Login_WebQuery()
Dim MyPost As String
Const MyUrl As String = "http://www.business-spreadsheets.com/forum.asp?t=103"
Const PostUser As String = "login=User Name" 'Change user name here
Const PostPassword As String = "&pass=password" 'Change password here
   
MyPost = PostUser & PostPassword
   
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Cells(1, 1))
    .PostText = MyPost
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
   
End Sub

 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 6 votes)
Russell,
Sorry for the delay, I'm in the military and recent world events put this valuation model (which is key to life after the military, as I'd like to get into stock analyst-type of work) was on the back-burner.

Now that I have some time over the next couple of days, I'd like to pick your brain a bit more.  

The website I'm trying to automate in Excel is www.investors.com specifically the login required in order to execute the web query.  Unfortunately I don't really understand which parameters I need to stick in the VBA code to automatically reference the cells holding the login and password.  Can you be a bit more descriptive for a newbie like me?  I really appreciate it...

 Posted by on
Surprised
Rate this:
(3/5 from 6 votes)
On analysis of the web site in question, the form variable names to login are htmUserName and htmPassword.

In this case, the primary task is to replace the three constants like:

VBA Code:
Const MyUrl As String = "http://www.investors.com/login.aspx?/default.asp"
Const PostUser As String = "htmUserName=User Name" 'Change user name here
Const PostPassword As String = "&htmPassword=password" 'Change password here


Note that, you may be able to change the URL and add more variables in order to download the data in one step without having to first login.
 Excel Business Forums Administrator
 Posted by on
Confused
Hi all,
I am trying to run a web query using VBA, but not able to pass through the login screen. I am using the following code for the query. 
I am trying access the website using the URL: [http://www.inkresearch.ca/coReport/coReport.php?ticker=AN]

When I access the website thorugh a browser, first I get a login page at [http://www.inkresearch.ca/]

Once logged in, I get http://www.inkresearch.ca/myInk.php , where I enter a ticker like 'AP' to go to the desired webpage.

Can you please suggest a solution to this problem?

Thanks,
MG.
VBA Code:
Public Sub InkWeb() Dim MyPost As String
Dim MyUrl As String
Dim PostUser As String
Dim PostPassword As String
   
   
   
MyUrl = Trim(Range("A1").Value)         ' URL
PostUser = Trim(Range("A2").Value)      ' Username
PostPassword = Trim(Range("A3").Value)  ' Password
   
MyPost = PostUser & PostPassword
   
With ActiveSheet.QueryTables.Add(Connection:= _
    "URL;" & MyUrl, Destination:=Cells(5, 1))
    .PostText = MyPost
    .BackgroundQuery = True
    .TablesOnlyFromHTML = True
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With
   
End Sub
 Posted by on
Confused
Rate this:
(3/5 from 6 votes)
I am new here and have just read this set of posts. Very useful info. I have the same problem with trying to login the [http://www.selftrade.co.uk/]. When I click Watchlists the login page comes up in a frame and there is a login tick box which seems to do a standard IE logon when clicked. I just cant see how to use the info in these posts on this site as it does not seem to do a logon using Post.

Any guidance on how to do this one.
 Posted by on
Confused
Rate this:
(2.2/5 from 4 votes)
Good code!

But I have this webpage, RECOM (Register Of Commerce):
http://recom1.onrc.ro/cgi-bin/recom.cgi?o7=1&l=R

I want to insert a CUI (Unique Identification Number -- of a Company) in the CUI field a return the result page.

A valid CUI number is: 15413161

So sticking 15413161 into CUI field and pressing "Selectie" should return a page wich will validate me or not the Company's Identification Number.

How can I adapt the code above for my case?

Thank you very much! :)

 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
What are the variable forms for username and pw for this site:http://www.bentekenergy.com/Benport/Login.aspx?ReturnUrl=%2fBenport%2fDisplayReportNews.aspx%3fLOC%3d1%26ID%3d0%26doc%3dBENTEK_EMF_NukeOutages.pdf&LOC=1&ID=0&doc=BENTEK_EMF_NukeOutages.pdf

 Thanks!
 Posted by on
Oops
Rate this:
(3/5 from 1 vote)
Go to the page: http://www.bentekenergy.com/Benport/Login.aspx and "View Source" HTML of the page. 

The tags like <input type="..." name="XXX" id="..." value="..." /> will tell you what form variables are needed where XXX is the name of the variable.

 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(3/5 from 1 vote)
I seem to have gotten the write variables from the source code, but when I run the code, I still get prompted with that box from the site asking for username and pw. Is there anything else I may need to tweak in the code to get past this??

Thank you!! 
 Posted by on
Surprised
Rate this:
(3/5 from 1 vote)
It could be due to the Javascript postback function on the login page.  Looking at the Javascript function, it seems that the target page for the login is in fact BENportHome.aspx and not Login.aspx.  Have you tried this?
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 5 

Find relevant Excel templates and add-ins for Automating a web query login to download data in the

 

Find Excel templates
and add-ins in the
Excel Business Solutions Directory
   
  © 2014 Business Spreadsheets. All Rights Reserved. Legal |