Home > Forum Home > Automating Data Analysis Tasks > Automating a web query login to download data Share

Automating a web query login to download data

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


Forum TopicPost Reply 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 11 to 20 of 40Order Replies By: Most Recent | Chronological | Highest Rated
Oops
Pass Variables to COM Add-in
Rate this:
(3/5 from 1 vote)
In the first instance, you should check what methods the COM Add-in supports. If there is a method to process login credentials then you should use this.
An alternative (though not very robust) is to use the SendKeys method, whereby you attempt to apply focus to the running application and then use the SendKeys command within VBA to mimic the login.  

 Excel Business Forums Administrator
 Posted by on
Confused
Fantastic start, but I'm still lost...
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
Confused
Web Query problem: not able to pass through login page
Rate this:
(3/5 from 6 votes)

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
Auto login for web query
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
variable forms for website...
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
Confused
right variables...wrong code..?
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
Confused
runtime 1004 error
Rate this:
(3/5 from 1 vote)
I am using the code you just posted - but i do get the run-time 1004 error, any ideas why..?
 Posted by on
Confused
still no luck...
Rate this:
(3/5 from 1 vote)
from [http://www.bentekenergy.com/Bentek/index.aspx]

 I get "a" and "b" for variables for username and pw respectively.
 from [http://www.bentekenergy.com/Benport/Login.aspx?ReturnUrl=/Benport/DisplayReportNews.aspx%3fLOC%3d1%26ID%3d0%26doc%3dBENTEK_EMF_NukeOutages.pdf&LOC=1&ID=0&doc=BENTEK_EMF_NukeOutages.pdf]
 I get "Login1$UserName" and "Login1$Password" for variables for username and pw respectively.
 Neither of those seem to work once. I also tried changing url from Login.aspx to BENportTech.aspx with no luck...... What else could I try to make this work...? I very much appreciate the help!

 Posted by on
Confused
so..
Rate this:
(3/5 from 1 vote)
I am going to need a bit of hand holding as I am a newb. How and what kind of if statement do I write? I am really not too familiar with vba and I really dont understand exactly what needs to be done...
 Posted by on
Confused
debugging
Rate this:
(3/5 from 1 vote)
I think the error is in this line:
 tempStr = Mid(x, StartPos, Len(x) - StartPos)
 Posted by on
 Displaying page 2 of 5 

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

© Business Spreadsheets   Legal   Follow us: