Results 1 to 3 of 3

Problems with importing data

Threaded View

gtg430i Problems with importing data 05-29-2017, 08:06 AM
davesexcel Re: Problems with importing... 05-29-2017, 08:13 AM
AB33 Re: Problems with importing... 05-29-2017, 09:14 AM
  1. #1
    Registered User
    Join Date
    03-26-2008
    Posts
    44

    Problems with importing data

    Hello - It has been a long time since I posted in this forum. I have had great help in the past.

    Here is my issue:

    I have an Excel/VBA file that imports historical data for stocks from Yahoo Finance. I used this file for few months and it was working with no issues.

    I travelled overseas and the model stops working at all. It keeps giving me the following errors:

    1 - Error 1004: The internet site reports that the item you requested could not be found.

    2 - Error 1004: time allocated.....


    I am very sure this the file itself is fine. This has to do maybe with the internet connection. Anyway id really appreciate if you can assist.

    Here is the code:

    Option Explicit
    
    Sub GetData()
        Dim DataSheet As Worksheet
        Dim EndDate As Date
        Dim StartDate As Date
        Dim Symbol As String
        Dim qurl As String
        Dim nQuery As Name
        Dim LastRow As Integer
        
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.Calculation = xlCalculationManual
        
        Sheets("Data").Cells.Clear
        
        Set DataSheet = ActiveSheet
      
            StartDate = DataSheet.Range("startDate").Value
            EndDate = DataSheet.Range("endDate").Value
            Symbol = DataSheet.Range("ticker").Value
            Sheets("Data").Range("a1").CurrentRegion.ClearContents
            
            qurl = "http://ichart.finance.yahoo.com/table.csv?s=" & Symbol
            qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
                "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
                Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Sheets("Data").Range("a1") & "&q=q&y=0&z=" & _
                Symbol & "&x=.csv"
                       
    QueryQuote:
                 With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets("Data").Range("a1"))
                    .BackgroundQuery = True
                    .TablesOnlyFromHTML = False
                    .Refresh BackgroundQuery:=False
                    .SaveData = True
                End With
                
                Sheets("Data").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Data").Range("a1"), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                    Semicolon:=False, Comma:=True, Space:=False, other:=False
                
             Sheets("Data").Columns("A:G").ColumnWidth = 12
    
        LastRow = Sheets("Data").UsedRange.Row - 2 + Sheets("Data").UsedRange.Rows.Count
    
        Sheets("Data").Sort.SortFields.Add Key:=Range("A2"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With Sheets("Data").Sort
            .SetRange Range("A1:G" & LastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
            .SortFields.Clear
        End With
    
    End Sub
    Last edited by gtg430i; 05-29-2017 at 09:30 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Importing from another worksheet problems.
    By excel-medic in forum Excel General
    Replies: 8
    Last Post: 09-29-2016, 04:32 AM
  2. Replies: 1
    Last Post: 02-17-2014, 01:39 PM
  3. Problems with importing XML files
    By chephy in forum Excel General
    Replies: 3
    Last Post: 01-17-2010, 01:28 PM
  4. Importing Data From Web Problems with XML
    By allencook in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-13-2009, 02:44 PM
  5. importing data problems
    By jaimetimbrell in forum Excel General
    Replies: 4
    Last Post: 06-17-2007, 09:34 AM
  6. [SOLVED] Formatting datetime problems importing data from mysql db
    By PEA in forum Excel General
    Replies: 2
    Last Post: 02-23-2006, 07:10 PM
  7. Importing External Data (Access) problems
    By jwrnana in forum Excel General
    Replies: 0
    Last Post: 09-01-2005, 05:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1