VBA To Automate Routine Task and Save Time

[zaska]

Hi,

I am in deep need of a VBA Code which could modify the below data to the desired format...At present i am manually doing the task which is consuming lot of time and boring too.


Actual Data
<table width="458" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:2706;width:56pt" width="74"> <col style="width:48pt" span="6" width="64"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl24" colspan="7" style="height:12.75pt; mso-ignore:colspan;width:344pt" width="458" height="17">http://www.nseindia.com/content/indices/histdata/BANK NIFTY30-05-2011-30-05-2011.csv</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">Date</td> <td class="xl24">Open</td> <td class="xl24">High</td> <td class="xl24">Low</td> <td class="xl24">Close</td> <td class="xl24">Shares Traded</td> <td class="xl24">Turnover (Rs. Cr)</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">30-May-11</td> <td class="xl24">10742</td> <td class="xl24">10823.25</td> <td class="xl24">10715.1</td> <td class="xl24">10781.45</td> <td class="xl24">10396116</td> <td class="xl24">966.65</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" colspan="7" style="height:12.75pt;mso-ignore:colspan" height="17">http://www.nseindia.com/content/indices/histdata/CNX 10030-05-2011-30-05-2011.csv</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">Date</td> <td class="xl24">Open</td> <td class="xl24">High</td> <td class="xl24">Low</td> <td class="xl24">Close</td> <td class="xl24">Shares Traded</td> <td class="xl24">Turnover (Rs. Cr)</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl25" style="height:12.75pt" height="17">30-May-11</td> <td class="xl24">5420.55</td> <td class="xl24">5440.2</td> <td class="xl24">5396.4</td> <td class="xl24">5411.05</td> <td class="xl24">1.62E+08</td> <td class="xl24">6187.08</td> </tr> </tbody></table>

Desired Output

SYMBOLNAME (Example : Banknifty) ...Date(YYYYMMDD) Open , High,Low,Close,SharesTraded

Example

BANKNIFTY , 20110530 ,10742,10823.25,10715.1,10781.45,1039611
CNX100,20110530,5420.55,5440.2,5396.40,5411.05,

1. Extracting the symbol name from the URL....(Totally there are 19 Symbols)



Thankyou

 

[MickG]

Try this results in Column "K"

[COLOR="Navy"]Sub[/COLOR] MG30May45
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] AC [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Left(Dn, 4) = "http" [COLOR="Navy"]Then[/COLOR]
            Txt = Replace(Mid(Dn, InStrRev(Dn, "/") + 1, InStr(Dn, "-") - (InStrRev(Dn, "/") + 3)), " ", "")
        [COLOR="Navy"]ElseIf[/COLOR] Left(Dn, 4) = "Date" [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]For[/COLOR] AC = 1 To 6
                [COLOR="Navy"]If[/COLOR] AC = 1 [COLOR="Navy"]Then[/COLOR]
                    Txt = Txt & "," & Format(Dn(2, AC), "yyyymmdd")
                [COLOR="Navy"]Else[/COLOR]
                    Txt = Txt & "," & Dn(2, AC)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] AC
                c = c + 1
                Ray(c) = Txt
                Txt = ""
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] Dn
        Range("K1").Resize(c) = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[zaska]

Thank you for the Quick help it worked for me