Extracting Data

[zaska]

Hi,

I want to extract Symbol Names In Column " A" From the Following URL's Kindly suggest VBA Code


<table width="582" border="0" cellpadding="0" cellspacing="0"><col width="582"><tr height="17"> <td style="height:12.75pt;width:437pt" width="582" height="17">http://www.nseindia.com/content/indices/histdata/BANK NIFTY30-05-2011-30-05-2011.csv

</td></tr></table>Extract BANKNIFTY

<table width="582" border="0" cellpadding="0" cellspacing="0"><col width="582"><tr height="17"> <td style="height:12.75pt;width:437pt" width="582" height="17">http://www.nseindia.com/content/indices/histdata/CNX 10030-05-2011-30-05-2011.csv</td> </tr></table>
Extract CNX 100

http://www.nseindia.com/content/indices/histdata/CNX ENERGY30-05-2011-30-05-2011.csv

Extract CNX ENERGY

Etc......

Thank you

 

[Ruddles]

The following function will take the URL string and extract the bit you're interested in. Paste the code into a new general code module.

Option Explicit
 
Public Function SymbolName(argURL As String) As String
 
  Dim iSlash As Integer
  Dim iHyphen As Integer
  
  iSlash = InStrRev(argURL, "/")
  iHyphen = InStr(argURL, "-")
  
  If iSlash = 0 Then Exit Function
  If iHyphen = 0 Then Exit Function
  If iHyphen < iSlash Then Exit Function
  
  SymbolName = Mid(argURL, iSlash + 1, iHyphen - iSlash - 3)
 
End Function

Call it in your worksheet like this:-

=SymbolName(A1)

Or in VBA like this:-

[I]somevariable[/I]=SymbolName(Range("A1").Value)

Test it thoroughly with all possible inputs before relying on it in an operational environment.

 

[zaska]

Thank you for the Kind support

It was very useful to me..

 

[zaska]

Hi, Can you Kindly explain the Logic in your code..

I am unable to understand how u accomplished this task and i am asking it out of curiosity to learn


Thank you

 

[Ruddles]

First we look for the last slash in the URL because the symbol name starts in the next position:-

iSlash = InStrRev(argURL, "/")

Then we look for the first hyphen because the symbol ends three positions before this:-

iHyphen = InStr(argURL, "-")


Next we check whether there are any problems with the URL: if there's no slash or no hyphen in it, then we won't be able to find the symbol, so we exit the function immediately. Also, if the hyphen occurs before the slash, something's wrong so again we exit immediately:-

If iSlash = 0 Then Exit Function
If iHyphen = 0 Then Exit Function
If iHyphen < iSlash Then Exit Function


Finally we take the part of the URL starting from the position after the slash and ending three positions before the hypen:-

SymbolName = Mid(argURL, iSlash + 1, iHyphen - iSlash - 3)

Hopefully the colour coding helps you follow the explanation?

 

[zaska]

That was very nice explanation.

Thank you very much

I understood it completely

Have a nice day