Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

Downloading Index

Hi Experts,

I have WB which download data from Web. I just have to put start date  and end date & it will download files for that time range & save to active WB path. I need little modification.
1 Save Path changes to "D:\AmiBroker Data\NSE\Index\"
2 Before downloading it clears all file from above location.
3 start downloading
4 print last date of available  file in Start date.
5 End

See attached file.

Thanks
NSE-Index-Importer.xlsm
0
Naresh Patel
Asked:
Naresh Patel
  • 6
  • 2
  • 2
3 Solutions
 
Naresh PatelTraderAuthor Commented:
Apology I won't be able to reply...as here 4:00 AM. Feeling sleepy.
0
 
byundtCommented:
I wish you had not password protected the VBA code, as I had to remove the protection prior to seeing what needed to be changed.

The code can really only be tested by you, given its dependence on certain data being available, but I believe the required changes are in the two subs below:
Sub DeleteFiles(flPath As String)       'Deletes all files from this folder
Dim fl As String
fl = Dir(flPath & "*.*")
Do Until fl = ""
    Kill flPath & fl
    fl = Dir
Loop
End Sub

Sub Macro1()
Dim flPath As String
Dim rgStart As Range
Dim WkBk As Excel.Workbook
Dim lastDate As Date
txtFileName As String

Application.ScreenUpdating = False
flPath = "D:\AmiBroker Data\NSE\Index\"
DeleteFiles flPath

Set rgStart = Range("C2")
startDate = Range("C2").Value
stopdate = Range("C3").Value

For xx = startDate To stopdate

    If HttpExists("http://nseindia.com/content/indices/ind_close_all_" & Format(xx, "ddmmyyyy") & ".csv") Then
        txtFileName = Format(xx, "ddmmyyyy")
        lastDate = xx
        
        Set WkBk = Workbooks.Add
    
        With ActiveSheet.QueryTables.Add(Connection:="URL;http://nseindia.com/content/indices/ind_close_all_" & Format(xx, "ddmmyyyy") & ".csv", Destination:=Range("$A$1"))
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlEntirePage
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .WebDisableRedirections = False
            .Refresh BackgroundQuery:=False
        End With
        Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
            Semicolon:=False, Comma:=True, Space:=False, Other:=False
        Application.DisplayAlerts = False
        WkBk.SaveAs Filename:=flPath & "\" & txtFileName & ".csv", FileFormat:=xlCSV, CreateBackup:=False
        WkBk.Close (False)
        Application.DisplayAlerts = True
    End If

Next xx

rgStart.Value = lastDate
Application.ScreenUpdating = True

MsgBox "Done"

End Sub

Open in new window

NSE-Index-ImporterQ28589869.xlsm
0
 
Naresh PatelTraderAuthor Commented:
Sir.Byundt,i got this error meassage.Thanks
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
byundtCommented:
That statement is missing the "Dim" keyword. I should have caught that, even without being able to test.
Sub Macro1()
Dim flPath As String
Dim rgStart As Range
Dim WkBk As Excel.Workbook
Dim lastDate As Date
Dim txtFileName As String

Open in new window

0
 
Naresh PatelTraderAuthor Commented:
Sir.Byundt,

Downloading files - Perfect. But when I clicked button it wont clear (Delete) Files from directory. Apology for delay in reply & please one favor - as start date print at the end of process is = last file available date, just need to add one day i.e. last date of file available = 1 jan 2015 then print date is +1 day which is 2 jan 2015.
I required in this manner  
1 when I clicked it clears all files from "D:\AmiBroker Data\NSE\Index" this directory.
2 Download files from start date to End date.
3 Print start date as last available file date plus one day.
4 End


Thank You
0
 
gowflowCommented:
I had to use byundt file as yours was pass protected.

Made necessary modification in sub delete files and in Macro as per your request.
gowflow
NSE-Index-Importer-V01.xlsm
0
 
Naresh PatelTraderAuthor Commented:
Awesome
0
 
Naresh PatelTraderAuthor Commented:
May I ask Follow Up?
0
 
gowflowCommented:
yes but pls no protected workbook
gowflow
0
 
Naresh PatelTraderAuthor Commented:
Here it is.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now