Automating a web query login to download data

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

Automating A Web Query Login To Download Data

Rate this: (2.8/5 from 16 votes)

	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: Go to "Data" on the Menu bar, then "Import External Data" Then go to "New Web Query" and a web browser will open Navigate to www.investors.com and login clicking remember me 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 kilo94 on 18 Dec 2006

Replies - Displaying 1 to 10 of 40

Order Replies By: Most Recent | Chronological | Highest Rated

	Post form variables to web query 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 Excel Helper on 23 Dec 2006

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 kilo94 on 27 Jan 2007

	Form variables required for login 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 Excel Helper on 29 Jan 2007

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 musicgold on 23 Mar 2007

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 DaveMills on 31 Oct 2007

POST Query difficulty 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 mircea80 on 14 Jun 2008

	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 vzny254 on 12 Nov 2009

	View the source of the login page 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 Excel Helper on 12 Nov 2009

	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 vzny254 on 12 Nov 2009

	Target page 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 Excel Helper on 12 Nov 2009

Displaying page 1 of 5

Next