Chaning the Date & Number Format in Array

[zaska]

Hi all,

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 sTemp As String
Dim iPtr As Long

 
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 = "E:\Macros\Output\Indices\" '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
       

        sTemp = ""

        For iPtr = LBound(bArray) To UBound(bArray)

         [COLOR=Red] sTemp = sTemp & Chr(bArray(iPtr))
[/COLOR] 
        Next iPtr

        sTemp = Replace(sTemp, "Date", "")

        sTemp = Replace(sTemp, "Open", "")

        sTemp = Replace(sTemp, "High", "")

        sTemp = Replace(sTemp, "Low", "")

        sTemp = Replace(sTemp, "Close", "")

        sTemp = Replace(sTemp, "Shares Traded", "")

        sTemp = Replace(sTemp, "Turnover (Rs. Cr)", "")

        If Left(sTemp, 2) = Chr(34) & Chr(34) Then

          sTemp = Mid(sTemp, 3)

        End If

        Do While Left(sTemp, 3) = "," & Chr(34) & Chr(34)

          sTemp = Mid(sTemp, 4)

        Loop

        If Left(sTemp, 1) = Chr(10) Then sTemp = Mid(sTemp, 2)

        ReDim bArray(Len(sTemp) - 1)

       

        Put #hFile, , arrURL(2, i) & "," & sTemp

 
              
       Next i

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

End Sub

This code generates the following output

<table width="512" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" span="8" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:48pt" width="64" align="left" height="17">S&P CNX NIFTY</td> <td class="xl22" style="width:48pt" width="64" align="center">08-Jun-11</td> <td style="width:48pt" width="64" align="center">5535.25</td> <td style="width:48pt" width="64" align="center">5556.6</td> <td style="width:48pt" width="64" align="center">5514.9</td> <td style="width:48pt" width="64" align="center">5526.85</td> <td style="width:48pt" width="64" align="center">92928675</td> <td style="width:48pt" width="64" align="right">3869.33</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">S&P CNX 500</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">4478.6</td> <td align="center">4496</td> <td align="center">4464.2</td> <td align="center">4473.25</td> <td align="center">3.78E+08</td> <td align="right">7750.05</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX 100</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">5473.7</td> <td align="center">5494.35</td> <td align="center">5454.6</td> <td align="center">5465.8</td> <td align="center">1.55E+08</td> <td align="right">5127.41</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX NIFTY JUNIOR</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">11369.65</td> <td align="center">11440.2</td> <td align="center">11341.85</td> <td align="center">11370.65</td> <td align="center">61920403</td> <td align="right">1258.08</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">NIFTY MIDCAP 50</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">2490.75</td> <td align="center">2505.4</td> <td align="center">2481.9</td> <td align="center">2490.2</td> <td align="center">72459873</td> <td align="right">857.9</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX MIDCAP</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">8042.55</td> <td align="center">8081.25</td> <td align="center">8023.8</td> <td align="center">8039.05</td> <td align="center">1.06E+08</td> <td align="right">1544.33</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX SMALLCAP</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">-</td> <td align="center">-</td> <td align="center">-</td> <td align="center">3738.67</td> <td align="center">77299628</td> <td align="right">924.76</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">BANK NIFTY</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">10816.4</td> <td align="center">10882.3</td> <td align="center">10764.7</td> <td align="center">10791.45</td> <td align="center">8225193</td> <td align="right">903.73</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX ENERGY</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">8757.1</td> <td align="center">8764.2</td> <td align="center">8719.9</td> <td align="center">8750</td> <td align="center">22208207</td> <td align="right">694.08</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX FMCG</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">9843.55</td> <td align="center">9956.7</td> <td align="center">9828.35</td> <td align="center">9935.25</td> <td align="center">16037701</td> <td align="right">369.05</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX INFRA</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">3000.15</td> <td align="center">3017.45</td> <td align="center">2993.1</td> <td align="center">3004.65</td> <td align="center">93262152</td> <td align="right">1097.78</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX IT</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">6684.1</td> <td align="center">6701.85</td> <td align="center">6652.2</td> <td align="center">6672.6</td> <td align="center">8356213</td> <td align="right">532.05</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX MNC</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">4996.9</td> <td align="center">5033.2</td> <td align="center">4989.05</td> <td align="center">5024.9</td> <td align="center">5204901</td> <td align="right">221.89</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX PHARMA</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">4848.45</td> <td align="center">4853.85</td> <td align="center">4787.55</td> <td align="center">4795.1</td> <td align="center">3609237</td> <td align="right">178.6</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX PSE</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">3338.75</td> <td align="center">3358.9</td> <td align="center">3333.35</td> <td align="center">3342.55</td> <td align="center">26649243</td> <td align="right">646.71</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX PSU BANK</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">3888.8</td> <td align="center">3901.15</td> <td align="center">3860.8</td> <td align="center">3870.8</td> <td align="center">5164186</td> <td align="right">447.97</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX REALTY</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">288.15</td> <td align="center">290.25</td> <td align="center">284.7</td> <td align="center">287.1</td> <td align="center">31415447</td> <td align="right">275.97</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">CNX SERVICE</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">6688.15</td> <td align="center">6711.35</td> <td align="center">6660.9</td> <td align="center">6676.8</td> <td align="center">46565382</td> <td align="right">1961.19</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="left" height="17">S&P CNX DEFTY</td> <td class="xl22" align="center">08-Jun-11</td> <td align="center">4287.85</td> <td align="center">4316</td> <td align="center">4279.25</td> <td align="center">4288.35</td> <td align="center">
</td> <td>
</td> </tr> </tbody></table>

I would like to change the Date Format to YYYYMMDD and Number format
to 0.00

Also I would like to delete the Last Column which is not highlighted in Red Colour

Replace " - " representing cells having no data to o


Thank you

 

[Norie]

Why do you insist on using an array?

 

[zaska]

Hi

Because the downloaded data is in array and i want to modify the array. I tried the other alternative as suggested by you previously. I tried recording a macro after downloading the data using the below code and changed it to the desired format. It was easy to handle the downloaded data.

I am trying to find the array solution too. Because i am not sure whether it is possible to Format the second element in an array. I.e ( Change Date Format ) and Delete the Last Element ( Turnover Column).

If you know the answer then kindly help me in this regard.

Thank you for any help.

bArray = oXMLHTTP.responseBody                  sTemp = ""          For iPtr = LBound(bArray) To UBound(bArray)           [COLOR=Red] sTemp = sTemp & Chr(bArray(iPtr)) [/COLOR]          Next iPtr          sTemp = Replace(sTemp, "Date", "")          sTemp = Replace(sTemp, "Open", "")          sTemp = Replace(sTemp, "High", "")          sTemp = Replace(sTemp, "Low", "")          sTemp = Replace(sTemp, "Close", "")          sTemp = Replace(sTemp, "Shares Traded", "")          sTemp = Replace(sTemp, "Turnover (Rs. Cr)", "")          If Left(sTemp, 2) = Chr(34) & Chr(34) Then            sTemp = Mid(sTemp, 3)          End If          Do While Left(sTemp, 3) = "," & Chr(34) & Chr(34)            sTemp = Mid(sTemp, 4)          Loop          If Left(sTemp, 1) = Chr(10) Then sTemp = Mid(sTemp, 2)          ReDim bArray(Len(sTemp) - 1)                   Put #hFile, , arrURL(2, i) & "," & sTemp </pre>

 

[zaska]

Waiting for some one to clarify the doubts.

Thank you

 

[zaska]

Hi,

I am in deep need for a solution to this problem

Thanks