What is the Problem in this Code to Pull Information from Google Finance

Ref Cross Post at other forum on this same topic: https://www.mrexcel.com/forum/excel-...ml#post5045934

I am attaching a worksheet. The purpose of this worksheet is to pull the name of the Companies (against the corresponding symbol) from Google Finance. This worksheet is not presently working and showing the runtime error.

I am trying to represent the whole thing at the level of my best....

How this Worksheet Operates:
At first step I put the list of the name of the symbols in the sheet entitled "List of Pre-added Symbols"

In the second step, in the Sheet "Symbols" I push the button "Copy Over" to copy the list of symbols from the sheet entitled "List of Pre-added Symbols". After that I push the button "Run". Immediately all the cells in the column "EXCHANGE" will be filled up "NSE", all the cells in the column "TYPE" will be filled up "STOCK", and all the cells in the column "Currency" will be filled up "INR", all the cells in the column "MINTICK" will be filled up BY "0.01" and the Cells in the Column "DESCRIPTION" will be be pulled from Google Finance Website. From the Google Finance Website the full name of the company against the corresponding symbol will be pulled in.

e.g if the symbol is "ADANIENT" => the exchange name will be "NSE", and the name of the company "Adani Enterprise Ltd" will be appeared in the corresponding "Description" column . this name will be fetched from the google finance site: https://www.google.com/search?tbm=fi...vgTKj6ngDg_5:0

similarly
e.g if the symbol is "ABB" => the exchange name will be "NSE", and the name of the company "ABB India Ltd" will be appeared in the corresponding "Description" column . this name will be fetched from the google finance site:https://www.google.com/search?tbm=fi...vQS2uqv4Bw_5:0

The case is like this:
Screenshot 1.jpg
Screenshot 2.jpg

The Runtime Error is coming like this:

Screenshot 3.PNG

Please help me to solve this issue.

Regards

Access denied is access denied, I think the issued is that and not the code.
Use debug and go through it step-by-step

@keebellah
thanks for your reply
I am almost no idea about VBA. I just use this sheet for my personal purpose. So can you please help to sort out the problem by what ever you are suggesting "By Using Debug"?

I perfectly understand that you are at loss, but if you use Excel with vba (macros and more) but have absolutely no knowledge of VBA it will be quite difficult to even try things out.
You must at least have some understanding.
I will download your file and see if I can find what is causing the problem (I doubt very much that I will be able to find it) but who knows.
I suggest you Google for VBA basics and start learning a little of it, will help you if you work more often with files like this.
The other option is to contact the person responsible for this file and it's code and have him/her help you.

First run.
Access denied, like I mentioned in the first place, no problem with the file, you do NOT HAVE ACCESS TO THE SITE, that is why it tell's you Access denied

The red bold marked text is where it stops

Please Login or Register  to view this content.

Happy PowerQuery to you.
Like I keep saying, never too old to learn

if you ask for too many (symbols/tickets) you will run into troubles = new google rules, so i think this is why your vba got access denied

We're sorry...

... but your computer or network may be sending automated queries. To protect our users, we can't process your request right now.

Rule 08: Cross-posting Without Telling Us

Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without telling us about your threads on other forums.

Post a link to any other forums where you have asked the same question. If you have fewer than 10 posts here, you will not be able to post a link, but you must still tell us where else you have asked the question.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

No further help to be offered, please, until the OP has complied with this request.

You need to supply a link to the post on the other forum.

Yes - thank you.

The same rule applies on most forums.

I agree with @sandy666
But I will take a look tomorrow (it's bedtime here) and see if I can see how 'access denied' could be avoided

You have provided the cross-post link so you can receive help.

I suggest you have a read of the forum rules to understand the expectations here: https://www.excelforum.com/forum-rul...rum-rules.html

I am not co-operating with you: I am applying the rules of the forum, with which YOU have now co-operated and must continue to do so going forward.

I am sure that Keebellah will help you once he is back online. You will need to wait patiently.

Hi guys, got stuck with a project I'm working on, hope to take a shot at it after dinner tonight, sorry for the delay

Thanks to @Sandy666 the Access denied is gone,
The code is abracadabra for me but it suns and does something in the 'Database' sheet.
The code stopped with an error because the last name in Database was not found.
I added a check if the Instr result is 0 to skip it but for the rest you will need somebody that understands how to do this for you.

Part of the VBA does work, you can see that the date is updated, and it is solvable but then we need to CLEARLY understand what the OP is trying to retrieve, the use of InStr as it is being used now is in many places incorrectly applied.

Below is a porting of the content of x and the company name NSE:ABAN is found but from there on it's the OP's responsability to tell us exactly what he/she wants

Please Login or Register  to view this content.

Instead of 'could' I would yes, your 'should' explain and if possible illustrate it.
I have attached in text form the retrieved content for NS:ABAN, that way you can see how it looks.

Mark the keywords of what you want and explain as much as possible, do it a Word document and paste color whatever to make it as clear as possible.

I have no knowledge of these things, only VBA

You will probably have to open the text file in NotePad++ to or some other to view it as XML

download this file: NSE-ABAN section.txt‎ on Desktop
you should see .txt
if not this is problem of your windows feature which hidding standard extensions
but if you see - click once on this file (to select it)
hit F2, go on the end then replace txt to html, then hit enter. (or twice enter and it should be opened in your default browser)

or

right click on this file in the post
save link as...
you'll see NSE-ABAN section.txt‎
change txt to html
then save

ss.jpg

@sandy666

here is that error
NSE-ABAN.PNG

The post shown is by Keebellah, not Sandy.

The link was to this post, actually: https://www.excelforum.com/excel-pro...ml#post4880379

Post the details in a post here, not in a Word document.

There is not enough information in the Word document to bother separating it from the thread. If it is all here in the thread, then it is easier for everyone to follow. Just copy what's in the document to your next post in this thread.

There are now over 60 posts in this thread. Members are trying very hard to help you and to make sense of what it is you are trying to do. I suspect they are probably getting tired, too, but they are sticking with you.

I have no idea what the problem is that you are now trying to explain - sorry.

Here are the contents of your Word file from post #58, which is what I asked you to post here:

There should be three sheets: Sheet 1 and Sheet 2
Give the name of “Sheet 1” => “Preloaded Symbols” this sheet will contain the list of symbols along with exchange, type, currency, minitick.

Now create Sheet 2 and Give the name of “Sheet 2” => “Symbols”. In this sheet (Symbols) create a button “Copy” on this sheet. Upon pressing this button all the columns will be copied from “Preloaded Symbols” =>“Symbols”. Create a button “Run” on this Sheet 2 (“Symbols”) which will grab the full names of the company (from Google Finance, as shown by screenshot in my #1 post) and will place those against the corresponding symbols.
Make a “Clear” button on “Sheet 2” (whose name is basically “Symbols”). The function of this button is to clean everything from “Sheet 2” and make it ready for next cycle, if be required.

You do NOT need to post the contents of Word files posted earlier in the thread.

All you need to do is post to it again when you are ready. We do not close or lock inactive threads.

@Ali
Ok after my recovery I am back here.
I am explaining here again what actually I require for:

1. Create a Workbook and Five any name to it.
2. In this workbook there will remain two sheets: "List of Symbols" and "Main Worksheet"
3. In the sheet "List of Symbols" there will remain a list of symbols (the total no of symbols may be 1500 like that).
4. In Another sheet "Main Worksheet" there will remain five columns : Symbols, exchange, type, currency, minitick and Description
Symbols.PNG
Main Worksheet.PNG

5. In the sheet "List of Symbols" there will remain a button "Copy" which will copy the total list of symbols to the 1st Column of "Main Worksheet"
6. Now this "Main Worksheet" will grab the full name of the symbol from google finance site. For example if it is SBIN, then Full name will be "State bank of India"
Similarly if it is YESBANK, then Full name will be "YESBANK", if it is UNITEDBNK , then Full name will be "United bank of India". Exchange will always be NSE.
Google Finance Page: https://www.google.com/search?stick=...vQTSx5LoBg_0:0

In the google finance page if anybody types "SBIN" immediately he see that NSE:SBIN is popping up as follows to get the page of SBIN
search symbol.jpg

Please let me know now is it clear?

I am glad you are better!

I cannot help with this query myself, however will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

Actually only he can deal this problem who has the expertise in grabbing data from Web.

Yes, obviously ...

I did say I couldn't help, but whoever does will need the file.

Forward what? This is a public forum. Every member has access to this thread!!!

You just need to be patient and wait for someone to come along who can help.

I've put out a call for help in the Call in the Cavalry thread.

For what it's worth. Here's my input on this matter.

To avoid access denied issue. You will need to do 1 of following.

1. Go to IE->Internet Options->Security->Custom Level->Miscellaneous. "Access data sources across domains" set it to Enable. And also, change http:// in the code to https://.

2. As stated by others, use "serverxmlhttp"

However, do note that Google finance heavily utilizes script to fill page and xml request will not be able to fetch result that you are after. 1 will return html source code with script to fetch data, but not the data itself. 2 will give you server side warning that you are not permitted to access the url using serverxmlhttp method.

The code you are using, looks like it is at least several years old and Google has changed things since then.

Your options are...
1. Pilot IE through VBA and scrape info (note that this will be much slower than xmlhttp each iteration will take at least 5 sec).
2. Find source other than Google that you can use to scrape the info.
3. Pay for API access.