Import from Y A H O O into Excel using Web-query!

#1
I'm making an excel to keep updating my current portfolio with the latest rates. For this I'm using web-query in excel to pull the LTP of the stock in the portfolio.

Now, I'm able to pull the necessary data properly in the required cell. But the problem is that the cell below becomes blank and I'm not able to add/assign a new web-query to it. Because of this I'm not able to get the required data in that cell.

Can someone please tell me what am I doing wrong in doing so? Also, what is the solution for the same?

My second query is that I want the web-query to automatically change and fetch corresponding data (LTP) depending on the script I change in the script list. I dont want to do it manually when I add, delete or replace anything in the list. As I dont know VBA, so it would be preferred if this can be done using some formula or technique, which I think would surely be there.

Though I'm new here, after going through the forum, it has inspired me a lot and will work towards posting some useful excels in the future. I would very much like to keep sharing my work and keep getting opinion on the same from you all seniors here.

Thanks and regards,

KCUBES.
 

Laksh

Active Member
#2
I'm making an excel to keep updating my current portfolio with the latest rates. For this I'm using web-query in excel to pull the LTP of the stock in the portfolio.

Now, I'm able to pull the necessary data properly in the required cell. But the problem is that the cell below becomes blank and I'm not able to add/assign a new web-query to it. Because of this I'm not able to get the required data in that cell.

Can someone please tell me what am I doing wrong in doing so? Also, what is the solution for the same?

My second query is that I want the web-query to automatically change and fetch corresponding data (LTP) depending on the script I change in the script list. I dont want to do it manually when I add, delete or replace anything in the list. As I dont know VBA, so it would be preferred if this can be done using some formula or technique, which I think would surely be there.

Though I'm new here, after going through the forum, it has inspired me a lot and will work towards posting some useful excels in the future. I would very much like to keep sharing my work and keep getting opinion on the same from you all seniors here.

Thanks and regards,

KCUBES.
Welcome to the forum! I used to use web query to get data from *****, though I am not using it now. To get the data of all the scrips of your interest you will first have to set up a portfolio of those scrips in *****. You can then use the ***** portfolio spreadsheet by web query to get the data for all those scrips in the portfolio. It is possible to auto refresh the web query sheet at intervals of > 1m, but not less. For doing this please see the help files about web query in excel.

Laksh
 

chachi

Active Member
#3
Mr Kcubes,

For your first query I have modified your uploaded file. Please check whether it is OK.

For your second query, I failed to understand exactly whats your requirement. Please elaborate or put it in a different way. There are plently of utlities here already posted, which might have covered your requirement.

Regards
Chachi
 
Last edited:

chachi

Active Member
#4
MrKcubes,

Further I noticed your extended requirement in the excel sheet uploaded by you and I am attaching an excel worksheet was used by me to earlier to track quotes from yhoo site (delayed) during market hours. Please note that you can add/modify your required scrips in column A:A, but without any gap, Dont leave any blank cells in between.

This sheet is a modifcation of one of the worksheet uploaded by one of the member here in this forum and is not created by me. Hope it is useful.

Chachi
 
Last edited:
#5
Mr Kcubes,

For your first query I have modified your uploaded file. Please check whether it is OK.

For your second query, I failed to understand exactly whats your requirement. Please elaborate or put it in a different way. There are plently of utlities here already posted, which might have covered your requirement.

Regards
Chachi
@ chachi
Firstly thanks for such quick reply.
I downloaded the file and tried to figure out what changes was done but was unable to understand how it was done. Except for naming the cells with the query itself. But I did not understand how other connections were added.

Also when refresh all is done, suzlon rate comes for a split second and then the cell becomes blank. So I think some changes needs to be done on the sheet.

Regards,
KCUBES
 
#6
MrKcubes,

Further I noticed your extended requirement in the excel sheet uploaded by you and I am attaching an excel worksheet was used by me to earlier to track quotes from yhoo site (delayed) during market hours. Please note that you can add/modify your required scrips in column A:A, but without any gap, Dont leave any blank cells in between.

This sheet is a modifcation of one of the worksheet uploaded by one of the member here in this forum and is not created by me. Hope it is useful.

Chachi
@chachi
As I mentioned earlier, I dont understand VBA. Also, when I use the refresh button it gives me error 'Compile error in hidden module: Macros'.
Moreover I would like to use this in the Excel which I'm making and will not be able to use the VBA thing without knowledge for the same.

Anyways thanks for the efforts that you have made.

I think the earlier sheet may give me my answer. Only some work with some clarification on how to incorporate is required.

On my second query:
I wanted that, if I change the name of the script in column C in the sheet, then webquery should automatically change accordingly and display respective data in column D. For example, if I change NTPC in cloumn C (C5) to say RIL, then in column D (D5), LTP of RIL should come instead of NTPC without me changing the webquery.
Hope you understand my 2nd query now and will be able to help me in this regard.

Thanks and regards,
KCUBES.
 

chachi

Active Member
#7
Mr. Kcubes,

I encountered the same problem you narrated in your first post when I tried at my office PC. I noticed that downloaded data occupies 2 cells (one below the other) and hence this problem. I tried and failed to rectify that, it is still amusing me. Try to leave a blank row in between and you get the desired result.

Sorry that my uploaded file is having problem. Please send your email id to me through message and I will mail you the working copy of GetQuote.xls

I strongly feel there is no way to get the desired result without using vba

Chachi
 
#8
If it is not possible to find a solution via some formula, can anyone write a VBA code for the same and attach as a text file here and explain how can I apply the same to to my excel to get the desired result.

Regards,

kcubes.
 

chachi

Active Member
#9
I'm making an excel to keep updating my current portfolio with the latest rates. For this I'm using web-query in excel to pull the LTP of the stock in the portfolio.

Now, I'm able to pull the necessary data properly in the required cell. But the problem is that the cell below becomes blank and I'm not able to add/assign a new web-query to it. Because of this I'm not able to get the required data in that cell.

KCUBES.
Hi Kcubes,

Just to solve your first query try this.

While creating the web query OR after creating the web query, go to data range properties and select "Overwrite existing cells with new data, clear unused cells". Create this for every stock you list.

if the above do not work, the following is a fool proof method....

copy http://download.finance.*****.com/d/quotes.csv?s=RNRL.NS+RPL.NS+NTPC.NS+ACC.NS&f=l1 into your web query and change/add the stock names as required. There seems to be a limitation for adding no. of stocks (may be around 10). This will list the prices one below the other.
 
#10
MrKcubes,

Further I noticed your extended requirement in the excel sheet uploaded by you and I am attaching an excel worksheet was used by me to earlier to track quotes from yhoo site (delayed) during market hours. Please note that you can add/modify your required scrips in column A:A, but without any gap, Dont leave any blank cells in between.

This sheet is a modifcation of one of the worksheet uploaded by one of the member here in this forum and is not created by me. Hope it is useful.

Chachi
Hi Chachi,
By using "GetQuoteNew" requested data is coming but not in formated form in DATA sheet.

Regard,
MK Singh
 

Similar threads