Small Macro Code To Extract Data

Z

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
Hi,

I have the following data

<table width="546" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:5924;width:122pt" width="162"> <col style="width:48pt" span="6" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:122pt" width="162" height="17">S&P CNX NIFTY"Date"</td> <td style="width:48pt" width="64">Open</td> <td style="width:48pt" width="64">High</td> <td style="width:48pt" width="64">Low</td> <td style="width:48pt" width="64">Close</td> <td style="width:48pt" width="64">Shares Traded</td> <td style="width:48pt" width="64">Turnover (Rs. Cr)</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl22" style="height:12.75pt" align="right" height="17">03-Jun-11</td> <td align="right">5565.7</td> <td align="right">5604.95</td> <td align="right">5507.2</td> <td align="right">5516.75</td> <td align="right">1.01E+08</td> <td align="right">4757.1</td> </tr> </tbody></table>

I need to change the above data to the following format


S&PCNXNIFTY,20110603,5565.70,5604.95,5507.20,5516.75,101017598

I have totally 19 symbols in 38 Rows ( Data filled in Two rows for each symbol like above example)

Finally i need only 19rows in the above format and delete the remaining rows.

Kindly request your help in this regard

The problem is in extracting the Symbol Name in Range "A1" to Range "A2" and then symbol name in Range "A3" to Range "A4" etc.,

Thank you
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Sort by date Sort by votes
What code are you using to get the data?
 
Upvote 0
Sir,

I am using the following code to get the data.

Code: 
Public Sub downloadNse()
Dim arrURL() As String
Dim dtmDate As Date
Dim c As Range
Dim i As Long
Dim s As String
Dim bArray() As Byte
Dim hFile As Integer
Dim strLocalFile As String
Dim oXMLHTTP As MSXML2.XMLHTTP '(reference to C:\Windows\System32\msxml2.dll for 32 bit systems)

    '-------------------------------------------------------------------------------
    'ENTER CONSTANTS HERE - NOTHING ELSE *SHOULD* NEED TO BE CHANGED
    Const CELL_WITH_DATE As String = "B1"
    Const RANGE_WITH_SYMBOLS As String = "A1:A19"
    Const SAVE_DIRECTORY As String = "C:\Macros\NSEIndices\" 'end with forward slash
    '-------------------------------------------------------------------------------
    
    dtmDate = Range(CELL_WITH_DATE).Value '//Date
    strLocalFile = SAVE_DIRECTORY & Format(dtmDate, "dd-mm-yyyy") & "_.csv"
    For Each c In Range(RANGE_WITH_SYMBOLS) '//11 cells with symbols
        If Len(c.Value) > 0 Then
            ReDim Preserve arrURL(1 To 2, 0 To i)
            arrURL(1, i) = "http://www.nseindia.com/content/indices/histdata/"
            arrURL(1, i) = arrURL(1, i) & UCase(c.Value)
            arrURL(2, i) = UCase(c.Value)
            arrURL(1, i) = arrURL(1, i) & Format(dtmDate, "dd-mm-yyyy") & "-" & Format(dtmDate, "dd-mm-yyyy") & ".csv"
            i = i + 1
        End If
    Next c
    
    'download the file from the web to the hardrive
    'loop through symbols in turn
    Set oXMLHTTP = New XMLHTTP
    hFile = FreeFile
    Open strLocalFile For Binary As #hFile
    For i = 0 To UBound(arrURL, 2)
        oXMLHTTP.Open "GET", arrURL(1, i), False
        oXMLHTTP.send
        'Wait for request to finish
        Do While oXMLHTTP.readyState <> 4
            DoEvents
        Loop
        
        bArray = oXMLHTTP.responseBody
        
        Put #hFile, , arrURL(2, i)
        Put #hFile, , bArray
              
       Next i

Handler:
On Error Resume Next
Close #hFile
Set oXMLHTTP = Nothing

End Sub

Thank you
 
Upvote 0
When you open the file(s) created by the code in a text editor like Notepad what format is the data in?
 
Upvote 0
Sir,

The above mentioned code generates only one single .csv file for all the 19 symbols.

When i Open the .csv file i get the data in the following format for Each Symbol in Two Rows.


Row1 : S&P CNX NIFTY"Date" Open High Low Close SharesTraded Turnover
Row2 : 03-Jun11 xxxx xxxx xxxx xxxx xxxxxxxx xxxxxx

I want to change this data into the following format

Row1 : S&P CNX NIFTY 20110603 value(open),value(high),value(Low),value(Close),value(SharesTraded)

similarly i want the same format for the remaining 18 Symbols...So finally i will have only 19 Rows instead of the original 38 Rows.

The Data File when opened with a NotePad looks like the below

S&P CNX NIFTY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 5565.70"," 5604.95"," 5507.20"," 5516.75"," 101017578"," 4757.10"
S&P CNX 500"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 4490.85"," 4525.30"," 4451.65"," 4457.90"," 366047803"," 8656.48"
CNX 100"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 5498.55"," 5538.25"," 5443.75"," 5452.35"," 148916125"," 5861.88"
CNX NIFTY JUNIOR"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 11387.65"," 11475.55"," 11287.85"," 11308.15"," 47898547"," 1104.78"
NIFTY MIDCAP 50"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 2495.70"," 2525.75"," 2475.55"," 2479.80"," 54632474"," 663.32"
CNX MIDCAP"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 8022.55"," 8101.90"," 7981.60"," 7992.95"," 113582648"," 1974.21"
CNX SMALLCAP"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011","-","-","-"," 3694.59"," 59990519"," 870.75"
BANK NIFTY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 10860.25"," 10946.75"," 10751.10"," 10782.00"," 10099081"," 1069.00"
CNX ENERGY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 8859.70"," 8910.45"," 8694.95"," 8710.10"," 17004441"," 676.37"
CNX FMCG"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 9960.85"," 10024.65"," 9881.55"," 9898.50"," 11462683"," 312.40"
CNX INFRA"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 3015.75"," 3044.40"," 3003.80"," 3010.50"," 102725531"," 1579.63"
CNX IT"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 6574.60"," 6633.15"," 6555.50"," 6562.05"," 7160419"," 461.98"
CNX MNC"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 5049.50"," 5072.55"," 5019.30"," 5047.50"," 6052408"," 250.92"
CNX PHARMA"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 4807.80"," 4831.70"," 4749.35"," 4759.40"," 3622455"," 196.01"
CNX PSE"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 3385.20"," 3406.50"," 3348.85"," 3355.85"," 16995092"," 431.13"
CNX PSU BANK"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 3896.50"," 3926.70"," 3852.75"," 3861.65"," 7385914"," 523.71"
CNX REALTY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 283.95"," 289.75"," 281.25"," 282.30"," 29018116"," 238.80"
CNX SERVICE"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
"03-Jun-2011"," 6701.90"," 6748.30"," 6637.95"," 6647.90"," 57585951"," 2415.86"
S&P CNX DEFTY"Date","Open","High","Low","Close"
"03-Jun-2011"," 4300.85"," 4329.75"," 4251.40"," 4259.40"




Thank you
 
Last edited:
Upvote 0
zaska,

Thanks for the Private Message.

I am not familar with your code.


If you import the text file into a worksheet, you will get this:


Excel Workbook
ABCDEFGHIJ
1S&P CNX NIFTY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
203-Jun-2011," 5565.70"," 5604.95"," 5507.20"," 5516.75"," 101017578"," 4757.10"
3S&P CNX 500"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
403-Jun-2011," 4490.85"," 4525.30"," 4451.65"," 4457.90"," 366047803"," 8656.48"
5CNX 100"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
603-Jun-2011," 5498.55"," 5538.25"," 5443.75"," 5452.35"," 148916125"," 5861.88"
7CNX NIFTY JUNIOR"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
803-Jun-2011," 11387.65"," 11475.55"," 11287.85"," 11308.15"," 47898547"," 1104.78"
9NIFTY MIDCAP 50"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
1003-Jun-2011," 2495.70"," 2525.75"," 2475.55"," 2479.80"," 54632474"," 663.32"
11CNX MIDCAP"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
1203-Jun-2011," 8022.55"," 8101.90"," 7981.60"," 7992.95"," 113582648"," 1974.21"
13CNX SMALLCAP"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
1403-Jun-2011,"-","-","-"," 3694.59"," 59990519"," 870.75"
15BANK NIFTY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
1603-Jun-2011," 10860.25"," 10946.75"," 10751.10"," 10782.00"," 10099081"," 1069.00"
17CNX ENERGY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
1803-Jun-2011," 8859.70"," 8910.45"," 8694.95"," 8710.10"," 17004441"," 676.37"
19CNX FMCG"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
2003-Jun-2011," 9960.85"," 10024.65"," 9881.55"," 9898.50"," 11462683"," 312.40"
21CNX INFRA"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
2203-Jun-2011," 3015.75"," 3044.40"," 3003.80"," 3010.50"," 102725531"," 1579.63"
23CNX IT"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
2403-Jun-2011," 6574.60"," 6633.15"," 6555.50"," 6562.05"," 7160419"," 461.98"
25CNX MNC"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
2603-Jun-2011," 5049.50"," 5072.55"," 5019.30"," 5047.50"," 6052408"," 250.92"
27CNX PHARMA"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
2803-Jun-2011," 4807.80"," 4831.70"," 4749.35"," 4759.40"," 3622455"," 196.01"
29CNX PSE"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
3003-Jun-2011," 3385.20"," 3406.50"," 3348.85"," 3355.85"," 16995092"," 431.13"
31CNX PSU BANK"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
3203-Jun-2011," 3896.50"," 3926.70"," 3852.75"," 3861.65"," 7385914"," 523.71"
33CNX REALTY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
3403-Jun-2011," 283.95"," 289.75"," 281.25"," 282.30"," 29018116"," 238.80"
35CNX SERVICE"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
3603-Jun-2011," 6701.90"," 6748.30"," 6637.95"," 6647.90"," 57585951"," 2415.86"
37S&P CNX DEFTY"Date","Open","High","Low","Close"
3803-Jun-2011," 4300.85"," 4329.75"," 4251.40"," 4259.40"
39
Sheet1





After the macro you will get this:


Excel Workbook
ABCDEFGHIJ
1S&P CNX NIFTY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"S&P CNX NIFTYDateOpenHighLowCloseSharesTradedTurnover
203-Jun-2011," 5565.70"," 5604.95"," 5507.20"," 5516.75"," 101017578"," 4757.10"S&P CNX NIFTY03-Jun-20115565.705604.955507.205516.751010175784757.10
3S&P CNX 500"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"S&P CNX 50003-Jun-20114490.854525.304451.654457.903660478038656.48
403-Jun-2011," 4490.85"," 4525.30"," 4451.65"," 4457.90"," 366047803"," 8656.48"CNX 10003-Jun-20115498.555538.255443.755452.351489161255861.88
5CNX 100"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"CNX NIFTY JUNIOR03-Jun-201111387.6511475.5511287.8511308.15478985471104.78
603-Jun-2011," 5498.55"," 5538.25"," 5443.75"," 5452.35"," 148916125"," 5861.88"NIFTY MIDCAP 5003-Jun-20112495.702525.752475.552479.8054632474663.32
7CNX NIFTY JUNIOR"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"CNX MIDCAP03-Jun-20118022.558101.907981.607992.951135826481974.21
803-Jun-2011," 11387.65"," 11475.55"," 11287.85"," 11308.15"," 47898547"," 1104.78"CNX SMALLCAP03-Jun-20113694.5959990519870.75
9NIFTY MIDCAP 50"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"BANK NIFTY03-Jun-201110860.2510946.7510751.1010782.00100990811069.00
1003-Jun-2011," 2495.70"," 2525.75"," 2475.55"," 2479.80"," 54632474"," 663.32"CNX ENERGY03-Jun-20118859.708910.458694.958710.1017004441676.37
11CNX MIDCAP"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"CNX FMCG03-Jun-20119960.8510024.659881.559898.5011462683312.40
1203-Jun-2011," 8022.55"," 8101.90"," 7981.60"," 7992.95"," 113582648"," 1974.21"CNX INFRA03-Jun-20113015.753044.403003.803010.501027255311579.63
13CNX SMALLCAP"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"CNX IT03-Jun-20116574.606633.156555.506562.057160419461.98
1403-Jun-2011,"-","-","-"," 3694.59"," 59990519"," 870.75"CNX MNC03-Jun-20115049.505072.555019.305047.506052408250.92
15BANK NIFTY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"CNX PHARMA03-Jun-20114807.804831.704749.354759.403622455196.01
1603-Jun-2011," 10860.25"," 10946.75"," 10751.10"," 10782.00"," 10099081"," 1069.00"CNX PSE03-Jun-20113385.203406.503348.853355.8516995092431.13
17CNX ENERGY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"CNX PSU BANK03-Jun-20113896.503926.703852.753861.657385914523.71
1803-Jun-2011," 8859.70"," 8910.45"," 8694.95"," 8710.10"," 17004441"," 676.37"CNX REALTY03-Jun-2011283.95289.75281.25282.3029018116238.80
19CNX FMCG"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"CNX SERVICE03-Jun-20116701.906748.306637.956647.90575859512415.86
2003-Jun-2011," 9960.85"," 10024.65"," 9881.55"," 9898.50"," 11462683"," 312.40"S&P CNX DEFTY03-Jun-20114300.854329.754251.404259.40
21CNX INFRA"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
2203-Jun-2011," 3015.75"," 3044.40"," 3003.80"," 3010.50"," 102725531"," 1579.63"
23CNX IT"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
2403-Jun-2011," 6574.60"," 6633.15"," 6555.50"," 6562.05"," 7160419"," 461.98"
25CNX MNC"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
2603-Jun-2011," 5049.50"," 5072.55"," 5019.30"," 5047.50"," 6052408"," 250.92"
27CNX PHARMA"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
2803-Jun-2011," 4807.80"," 4831.70"," 4749.35"," 4759.40"," 3622455"," 196.01"
29CNX PSE"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
3003-Jun-2011," 3385.20"," 3406.50"," 3348.85"," 3355.85"," 16995092"," 431.13"
31CNX PSU BANK"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
3203-Jun-2011," 3896.50"," 3926.70"," 3852.75"," 3861.65"," 7385914"," 523.71"
33CNX REALTY"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
3403-Jun-2011," 283.95"," 289.75"," 281.25"," 282.30"," 29018116"," 238.80"
35CNX SERVICE"Date","Open","High","Low","Close","Shares Traded","Turnover (Rs. Cr)"
3603-Jun-2011," 6701.90"," 6748.30"," 6637.95"," 6647.90"," 57585951"," 2415.86"
37S&P CNX DEFTY"Date","Open","High","Low","Close"
3803-Jun-2011," 4300.85"," 4329.75"," 4251.40"," 4259.40"
39
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub ExtractData()
' hiker95, 06/04/2011
' http://www.mrexcel.com/forum/showthread.php?t=554870
Dim LR As Long, a As Long, NR As Long
Dim Sp, s As Long
Application.ScreenUpdating = False
Range("C1:J1") = [{"S&P CNX NIFTY","Date","Open","High","Low","Close","SharesTraded","Turnover"}]
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To LR Step 2
  NR = Range("C" & Rows.Count).End(xlUp).Offset(1).Row
  Cells(NR, 3) = Left(Cells(a, 1), Application.Find("""", Cells(a, 1), 1) - 1)
  Sp = Split(Cells(a + 1, 1), ",")
  For s = LBound(Sp) + 1 To UBound(Sp)
    If InStr(Sp(s), " ") > 0 Then Sp(s) = Replace(Sp(s), " ", "")
    If InStr(Sp(s), Chr(34)) > 0 Then Sp(s) = Replace(Sp(s), Chr(34), "")
    If InStr(Sp(s), Chr(45)) > 0 Then Sp(s) = ""
  Next s
  Cells(NR, 4).Resize(, UBound(Sp) + 1).Value = Sp
Next a
Range("D2:D" & NR).NumberFormat = "dd-mmm-yyyy"
Range("E2:H" & NR).NumberFormat = "0.00"
Range("I2:I" & NR).NumberFormat = "0"
Range("J2:J" & NR).NumberFormat = "0.00"
Columns("C:J").AutoFit
Application.ScreenUpdating = True
End Sub


Import the text file into a worksheet.


Then run the ExtractData macro.
 
Upvote 0
Another Try:
Code: 
[COLOR=Navy]Public[/COLOR] [COLOR=Navy]Sub[/COLOR] downloadNse()
[COLOR=Navy]Dim[/COLOR] arrURL() [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] dtmDate [COLOR=Navy]As[/COLOR] [COLOR=Navy]Date[/COLOR]
[COLOR=Navy]Dim[/COLOR] c [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] i [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] s [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] strResponse_Text [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] strLocalFile [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] oXMLHTTP [COLOR=Navy]As[/COLOR] MSXML2.XMLHTTP [COLOR=SeaGreen]'(reference to C:\Windows\System32\msxml2.dll for 32 bit systems)[/COLOR]
[COLOR=Navy]Dim[/COLOR] FSO [COLOR=Navy]As[/COLOR] [COLOR=Navy]Object[/COLOR] [COLOR=SeaGreen]'FileSystemObject[/COLOR]
[COLOR=Navy]Dim[/COLOR] ts [COLOR=Navy]As[/COLOR] [COLOR=Navy]Object[/COLOR] [COLOR=SeaGreen]'TextStream[/COLOR]
[COLOR=Navy]Dim[/COLOR] re [COLOR=Navy]As[/COLOR] [COLOR=Navy]Object[/COLOR] [COLOR=SeaGreen]'RegExp[/COLOR]

    [COLOR=SeaGreen]'-------------------------------------------------------------------------------[/COLOR]
    [COLOR=SeaGreen]'ENTER CONSTANTS HERE - NOTHING ELSE *SHOULD* NEED TO BE CHANGED[/COLOR]
    [COLOR=Navy]Const[/COLOR] CELL_WITH_DATE [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR] = "B1"
    [COLOR=Navy]Const[/COLOR] RANGE_WITH_SYMBOLS [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR] = "A1:A19"
    [COLOR=Navy]Const[/COLOR] SAVE_DIRECTORY [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR] = "C:\myTemp\" [COLOR=SeaGreen]'"E:\Macros\Output\Indices\" 'end with forward slash[/COLOR]
    [COLOR=SeaGreen]'-------------------------------------------------------------------------------[/COLOR]
    
    dtmDate = Range(CELL_WITH_DATE).Value [COLOR=SeaGreen]'//Date[/COLOR]
    strLocalFile = SAVE_DIRECTORY & Format(dtmDate, "dd-mm-yyyy") & "_.csv"
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] c [COLOR=Navy]In[/COLOR] Range(RANGE_WITH_SYMBOLS)
        [COLOR=Navy]If[/COLOR] Len(c.Value) > 0 [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]ReDim[/COLOR] [COLOR=Navy]Preserve[/COLOR] arrURL(1 [COLOR=Navy]To[/COLOR] 2, 0 [COLOR=Navy]To[/COLOR] i)
            arrURL(1, i) = "http://www.nseindia.com/content/indices/histdata/"
            arrURL(1, i) = arrURL(1, i) & UCase(c.Value)
            arrURL(2, i) = UCase(c.Value)
            arrURL(1, i) = arrURL(1, i) & Format(dtmDate, "dd-mm-yyyy") & "-" & Format(dtmDate, "dd-mm-yyyy") & ".csv"
            i = i + 1
        [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    [COLOR=Navy]Next[/COLOR] c
    
    [COLOR=SeaGreen]'get the data from the web and loop through symbols in turn[/COLOR]
    [COLOR=Navy]Set[/COLOR] oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
    [COLOR=Navy]Set[/COLOR] re = CreateObject("VBScript.RegExp")
    [COLOR=Navy]Set[/COLOR] FSO = CreateObject("Scripting.FileSystemObject")
    [COLOR=Navy]Set[/COLOR] ts = FSO.CreateTextFile(strLocalFile, True)
    
    [COLOR=Navy]For[/COLOR] i = 0 [COLOR=Navy]To[/COLOR] UBound(arrURL, 2)
        oXMLHTTP.Open "GET", arrURL(1, i), False
        oXMLHTTP.send
        [COLOR=Navy]Do[/COLOR] [COLOR=Navy]While[/COLOR] oXMLHTTP.readyState <> 4
            DoEvents
        [COLOR=Navy]Loop[/COLOR]
        
        strResponse_Text = oXMLHTTP.responseText
        
        [COLOR=SeaGreen]'Clean up text and write to file[/COLOR]
        s = Replace(strResponse_Text, Chr(34), "")             [COLOR=SeaGreen]'strip unneeded double quotes[/COLOR]
        s = arrURL(2, i) & vbLf & s                            [COLOR=SeaGreen]'add symbol and LF delimiter[/COLOR]
        s = Split(s, vbLf)(0) & ", " & Split(s, vbLf)(2)       [COLOR=SeaGreen]'split on LF delimiters --> result is {symbol|headers|values}[/COLOR]
        s = Trim_Inner(re, s)                                  [COLOR=SeaGreen]'remove extra spaces[/COLOR]
        ts.WriteLine s                                         [COLOR=SeaGreen]'write to file[/COLOR]
        
        [COLOR=Navy]Debug[/COLOR].[COLOR=Navy]Print[/COLOR] arrURL(2, i) & " " & strResponse_Text
        [COLOR=Navy]Debug[/COLOR].[COLOR=Navy]Print[/COLOR] "-----------------"
        
       [COLOR=Navy]Next[/COLOR] i

Handler:
[COLOR=Navy]On[/COLOR] [COLOR=Navy]Error[/COLOR] [COLOR=Navy]Resume[/COLOR] [COLOR=Navy]Next[/COLOR]
[COLOR=Navy]If[/COLOR] [COLOR=Navy]Not[/COLOR] ts [COLOR=Navy]Is[/COLOR] [COLOR=Navy]Nothing[/COLOR] [COLOR=Navy]Then[/COLOR]
    ts.Close
    [COLOR=Navy]Set[/COLOR] ts = [COLOR=Navy]Nothing[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
[COLOR=Navy]Set[/COLOR] FSO = [COLOR=Navy]Nothing[/COLOR]
[COLOR=Navy]Set[/COLOR] re = [COLOR=Navy]Nothing[/COLOR]
[COLOR=Navy]Set[/COLOR] oXMLHTTP = [COLOR=Navy]Nothing[/COLOR]

[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
[COLOR=Navy]Private[/COLOR] [COLOR=Navy]Function[/COLOR] Trim_Inner(ByRef re [COLOR=Navy]As[/COLOR] RegExp, [COLOR=Navy]ByVal[/COLOR] arg [COLOR=Navy]As[/COLOR] String) [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=SeaGreen]'//To reduce multiple spaces to a single space within a string[/COLOR]
    [COLOR=Navy]With[/COLOR] re
        .Pattern = "[  ]+"
        .Global = True
        .MultiLine = True
    
        [COLOR=Navy]If[/COLOR] .Test(arg) [COLOR=Navy]Then[/COLOR]
            Trim_Inner = .Replace(arg, " ")
        [COLOR=Navy]Else[/COLOR]
            Trim_Inner = arg
        [COLOR=Navy]End[/COLOR] [COLOR=Navy]If[/COLOR]
    
    [COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
    
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Function[/COLOR]
Sample Data in the Excel sheet:
Excel 2003<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th><th>B</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">S&P CNX NIFTY</td><td style="text-align: right;;">6/1/2011</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">S&P CNX 500</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">CNX 100</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">CNX NIFTY JUNIOR</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">NIFTY MIDCAP 50</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">CNX MIDCAP</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">CNX SMALLCAP</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">BANK NIFTY</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">CNX ENERGY</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">CNX FMCG</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">CNX INFRA</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">CNX IT</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">13</td><td style=";">CNX MNC</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">14</td><td style=";">CNX PHARMA</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">15</td><td style=";">CNX PSE</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">16</td><td style=";">CNX PSU BANK</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style=";">CNX REALTY</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style=";">CNX SERVICE</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style=";">S&P CNX DEFTY</td><td style="text-align: right;;">
</td></tr></tbody></table>
Sheet1


Seems like you don't always get all the information. Sometimes all the stocks downloaded successfully but not always. Sample results:
CNX MIDCAP, 01-Jun-2011, 8065.50, 8111.10, 8063.05, 8102.80, 110060345, 1610.80
CNX SMALLCAP, <H1>Not Found</H1> The requested object does not exist on this server. The link you followed is either outdated, inaccurate, or the server has been instructed not to let you have it.
BANK NIFTY, 01-Jun-2011, 11011.20, 11056.40, 10959.10, 11033.40, 11294940, 1298.74
​
 
Last edited:
Upvote 0
BTW, I rather like Hiker's solution and it's a good way to handle this - first get the "Raw" data, then parse it out. Anyway, either way really.

The biggest difference in my new code is that we are using the response text rather than the response body, which we can store in a string rather than as an array of byte values. Easier to work with after that using typical string handling to parse the return values from each xmlhttp request.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Macro Help - Transposing Some Data from Columns to Rows, Accounting for Duplicate Data
Replies
1
Views
127
Kaper
Kaper
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
J
  • Question Question
Power Query editor OR VBA to extract data from Grants.gov
Replies
3
Views
377
Jagadeshrao
J
D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
366
dilshod_k
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,597
Messages
6,179,813
Members
452,945
Latest member
Bib195

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top