Real Time Stock Price from Google Finance in Excel

Hi All;

I want to download google finance data from this url :-
http://www.google.com/finance/getpri...=1266701290218
where
q= stock symbol on Google finance
x= exchange symbol
i= interval (here 60 means 60 sec (1 minute interval))
p= no of period(here 5d denotes 5 days of data)
f= parameters (day, close, open, high and low)
df= difference (cpct is may be in % change )
auto =1,

and than want to save that in .txt file formate with that stock symbol.

Here I am attaching one excel file which is downloading the data, but i want to save that data in .txt formate as well.
here is the link of that excel file as well :- http://investexcel.net/google-financ...uotes-in-excel

tnx in advance

To add saving data as text file feature, add these lines to existing code :

Please Login or Register  to view this content.

Maybe this :

Please Login or Register  to view this content.

You just found a bug in the macro (from investexcel.net isn't it), you can contact the author to tell him about this.

His method is using QueryTable to fetch the data from GoogleFinance, the result of this fetch is one column of data (at column A), he then use TextToColumn to split the data to multiple columns. Under normal circumstances, the content of each cell should be in string datatype (so the TextToColumn using comma as delimited character can be used), but this GAIL symbol is something unexpected. For the row(s) where the error occurred, the cell is in numeric datatype.

You can examine this by adding "Exit Sub" statement before the code reach TextToColumn job :

Please Login or Register  to view this content.

Now if you look at sheet "Data", for cell A150, A151, A215, A250, the datatype for these cells is numeric (they are right aligned), and if you change the number format to General for example, the format is ruined. This is not happened with other "normal" cells, because on these normal cells, there is mixed of commas and dots, so Excel automatically treats them as string (so can be splitted using TextToColumn using comma as delimiter), but for the failed cells, Excel assumes the comma(s) is just a thousand separator symbol (no comma at all), so the TextToColumn is failed.

And since it is not always the data is grouped in 3 digits format (for example the cell A250, there is one extra digit compared to other failed cells), we cannot manually fix the error.

-----------------------------------

Here I use another method to fetch the data using WinHTTPRequest object, the return is in string datatype (so no automatic data Conversion as occurred using QueryTable), and then split manually (not using TextToColumn feature). It seemed that the result is correct :

Please Login or Register  to view this content.

Thank you.

To remove the header, change this line :

Please Login or Register  to view this content.

To :

Please Login or Register  to view this content.

You are welcome, glad to help.


Regards

Hi karedog,

I am downloading data from this link http://www.nseindia.com/content/hist...14bhav.csv.zip from VBA like below method

Please Login or Register  to view this content.

But from today I got "Access Denied" msg ...I tried to find the solution on the net but I didn't get success...what I get is this link which I am not
able to understand bcoz its for R ...
http://stackoverflow.com/questions/2...-with-get-in-r

here they said "You need to set a browser-like user agent string so the site thinks you're a browser vs an automated scraper/downloader robot:"

SO HOW CAN I DO THIS IN EXCEL VBA ?? Any Help please dear ....

Here I have also attach the screen shot of Error which I got from server

@chirag_patel5141

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

I can't open that page with my browser either, which means it is not user agent related problem.

But if try to open other page from the same website, for example : https://www.nseindia.com/archives/eq...r/PR210616.zip
this page can be opened without a problem, so it seemed that this is a temporary server side problem.

You can see the same thing from this webpage :
http://answers.microsoft.com/en-us/w...8a0d67e?auth=1

The asker said :
"Well, today it works just fine without me having to do anything."

so you may want to wait 1 or 2 days to see if the problem is gone by itself (fixed from serverside).