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 :

    Application.Calculation = xlCalculationAutomatic

    qurl = ThisWorkbook.Path & "\" & ticker & ".txt"
    On Error Resume Next
       Kill qurl
    On Error GoTo 0
    DataSheet.Copy
    ActiveSheet.SaveAs Filename:=qurl, FileFormat:=xlTextMSDOS
    Application.DisplayAlerts = False
       ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

Maybe this :

    Application.Calculation = xlCalculationAutomatic

    qurl = ThisWorkbook.Path & "\" & ticker & ".txt"
    On Error Resume Next
       Kill qurl
    On Error GoTo 0
    DataSheet.Copy
    With ActiveSheet
      .Rows("1:6").Delete xlShiftUp
      With .Range("A1").CurrentRegion.Resize(, 8).Offset(, 8)
        .Formula = Array(ticker, "=TEXT(G1,""YYYYMMDD"")", "=TEXT(G1,""HH:MM:"")&""00""", "=E1", "=C1", "=D1", "=B1", "=F1")
        .Rows(1).Value = Array("Symbol", "Date", "Time", "OPEN", "HIGH", "LOW", "CLOSE", "VOLUME")
        .Rows(.Rows.Count).ClearContents
        .Value = .Value
      End With
      .Columns("A:H").Delete xlShiftToLeft
      .Columns("C").NumberFormat = "HH:MM:SS"
      .SaveAs Filename:=qurl, FileFormat:=xlCSV
    End With
    Application.DisplayAlerts = False
       ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub

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 :

    Exit Sub
    DataSheet.Range("a1").CurrentRegion.TextToColumns Destination:=DataSheet.Range("a1"), DataType:=xlDelimited, _
                                                      TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                                      Semicolon:=False, Comma:=True, Space:=False, other:=False

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 :

Sub GetData2()
  Dim interval As Long, numPastTradingDays As Long, timeStamp As Long, timeZoneOffset As Long, exchange As String, ticker As String, qurl As String
  Dim i As Long, strOut As String, v1, v2, vDate

  With Sheets("Parameters")
    ticker = .Range("ticker").Value
    exchange = .Range("exchange").Value
    interval = .Range("interval").Value
    numPastTradingDays = .Range("numTradingDays").Value
  End With
  qurl = "http://www.google.com/finance/getprices?" & "q=" & ticker & "&i=" & interval & "&p=" & numPastTradingDays & "d" & "&f=d,o,h,l,c,v"

  With CreateObject("WINHTTP.WinHTTPRequest.5.1")
    .Open "GET", qurl, False
    .send
    v1 = Split(.responseText, Chr$(10))
  End With
  timeZoneOffset = CLng(Split(v1(6), "=")(1))
  strOut = "Symbol,Date,Time,OPEN,HIGH,LOW,CLOSE,VOLUME" & vbCrLf
  For i = 7 To UBound(v1) - 1
      v2 = Split(v1(i), ",")
      If Not IsNumeric(v2(0)) Then
         timeStamp = CLng(Mid$(v2(0), 2)) + timeZoneOffset * 60
         vDate = timeStamp / 86400 + 25569
      Else
         vDate = (CLng(v2(0)) * interval + timeStamp) / 86400 + 25569
      End If
      strOut = strOut & ticker & "," & Format$(vDate, "YYYYMMDD") & "," & Format$(vDate, "HH:MM:") & "00," & v2(4) & "," & v2(2) & "," & v2(3) & "," & v2(1) & "," & v2(5) & vbCrLf
  Next i
  Open ThisWorkbook.Path & "\" & ticker & ".txt" For Output As #1
    Print #1, strOut
  Close #1
End Sub

Thank you.

To remove the header, change this line :

strOut = "Symbol,Date,Time,OPEN,HIGH,LOW,CLOSE,VOLUME" & vbCrLf

To :

strOut = ""

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

    Dim sURL    As String
    Dim LocalFilename   As String
    Dim filename As String
    Dim objZip
    
    'You Can Change Directory Here
    Const UNC = "C:\BhavCopy\"
    FName = Cells(7, 3)
    zipfilename = FName & ".zip"
    
    'Write website address to download Bhavcopy here
    sURL = "http://www.nseindia.com/content/historical/DERIVATIVES/" & Cells(9, 3) & zipfilename
    LocalFilename = UNC & zipfilename

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] [/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).