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)?
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:
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
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...
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:
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.
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
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.
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
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??