Concatenating Using VBA based on Expiry Date

Z

zaska

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

<table border="0" cellpadding="0" cellspacing="0" width="302"><col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:78pt" height="20" width="104"> <table border="0" cellpadding="0" cellspacing="0" width="302"><col style="mso-width-source:userset;mso-width-alt:3803;width:78pt" width="104"> <col style="mso-width-source:userset;mso-width-alt:4059;width:83pt" width="111"> <col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:78pt" height="20" width="104">FUTSTK</td> <td style="width:83pt" width="111">VOLTAS</td> <td class="xl65" style="width:65pt" align="right" width="87">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>VOLTAS</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WELCORP</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WELCORP</td> <td class="xl65" align="right">25-Aug-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WELCORP</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WIPRO</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>WIPRO</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>YESBANK</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>YESBANK</td> <td class="xl65" align="right">25-Aug-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>YESBANK</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>ZEEL</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>ZEEL</td> <td class="xl65" align="right">25-Aug-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTSTK</td> <td>ZEEL</td> <td class="xl65" align="right">29-Sep-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">OPTIDX</td> <td>BANKNIFTY</td> <td class="xl65" align="right">28-Jul-11</td> </tr> </tbody></table></td> <td class="xl63" style="border-left:none;width:83pt" width="111">
</td> <td class="xl63" style="border-left:none;width:65pt" width="87">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">I want to accomplish the following from the above table.

1. Delete all the Data for the "OPTIDX" Symbols. I want data only for the rows having "FUTSTK"

2. Append "-I" for 1st expiry date i.e 28-Jul-2011 and "-II" for 2nd Expiry Date i.e 29-Sep-2011 and "-III" for 3rd Expiry date.

Desired Output

VOLTAS-I
VOLTAS-II
WELCORP-I
WELCORP-II
WELCORP-III
WIPRO-I
WIPRO-II
YESBANK-I
YESBANK-II
YESBANK-III

Thank you


</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">
</td> <td class="xl63" style="border-top:none;border-left:none">
</td> <td class="xl64" style="border-top:none;border-left:none" align="right">
</td> </tr> </tbody></table>
 
Sort by date Sort by votes
Sir,

I got Error 1004 Application Defined or Object Defined Error. This macro is stored in NSEConverter.xls.. which also contains the data in sheet1 as referred in lookup formula.


Code: 
Sub test()
ChDir "E:\Macros\Input"
Workbooks.Open Filename:="E:\Macros\Input\fo" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmmyyyy") & "bhav.csv", Origin:=xlWindows
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
 With Range("A1:O" & LastRow)
     .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"
     .Offset(1, 0).EntireRow.Delete
 End With
 ActiveSheet.AutoFilterMode = False
 LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("C").Insert
    Range("C2").Value = "-I"
For i = 3 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
Next i
For i = 2 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
    Range("D" & i).NumberFormat = "yyyymmdd"
    Range("D" & i).Value = Range("P" & i).Value
Next i
Columns("C").Delete
Columns("N:O").Delete
Columns("L").Delete
Columns("I:J").Delete
Columns("D:E").Delete
Columns("A").Delete
Range("B1").Value = "TIMESTAMP"
Range("H2:H" & LR).Formula = "=F2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&[E:\Macros\NseConverter.xls]Sheet1!$A$1:$A$226,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),[E:\Macros\NseConverter.xls]Sheet1!$B$1:$B$226)"
Application.ScreenUpdating = True
ChDir "E:\Macros\Output"
ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\Stocks\fo" & Format(Date, "ddmmmyyyy") & "bhav.txt", FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close
 End Sub

Thanks
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
If it is a sheet in the same workbook then

Code: 
Sub test()
ChDir "E:\Macros\Input"
Workbooks.Open Filename:="E:\Macros\Input\fo" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmmyyyy") & "bhav.csv", Origin:=xlWindows
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
 With Range("A1:O" & LastRow)
     .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"
     .Offset(1, 0).EntireRow.Delete
 End With
 ActiveSheet.AutoFilterMode = False
 LR = Range("A" & Rows.Count).End(xlUp).Row
    Columns("C").Insert
    Range("C2").Value = "-I"
For i = 3 To LR
    If Range("B" & i).Value = Range("B" & i - 1).Value Then
        Range("C" & i).Value = Range("C" & i - 1).Value & "I"
    Else
        Range("C" & i).Value = "-I"
    End If
Next i
For i = 2 To LR
    Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value
    Range("D" & i).NumberFormat = "yyyymmdd"
    Range("D" & i).Value = Range("P" & i).Value
Next i
Columns("C").Delete
Columns("N:O").Delete
Columns("L").Delete
Columns("I:J").Delete
Columns("D:E").Delete
Columns("A").Delete
Range("B1").Value = "TIMESTAMP"
Range("G2:G" & LR).Formula = "=F2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&Sheet1!$A$1:$A$226,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),Sheet1!$B$1:$B$226)"
Application.ScreenUpdating = True
ChDir "E:\Macros\Output"
ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\Stocks\fo" & Format(Date, "ddmmmyyyy") & "bhav.txt", FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close
 End Sub
 
Upvote 0
No sir it is not the sheet in the same workbook. As you can see in the code that i am opening fo.csv file from E:\Macros\Input

Actually the data is stored in Sheet1 of NSEConverter.xls . This workbook(NSEConverter.xls ) is holding the data as well as the macro code.
 
Upvote 0
If the red coloured text is the correct path to the sheet it should work

Rich (BB code): 
Range("H2:H" & LR).Formula = "=F2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&[E:\Macros\NseConverter.xls]Sheet1!$A$1:$A$226,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),[E:\Macros\NseConverter.xls]Sheet1!$B$1:$B$226)"

Does the formula work if you enter it manually referring to the sheet in another workbook?

If you can tell me what the full formula should be I can give you the VBA to insert it in a range.
 
Upvote 0
Sir,

Let me tell the whole thing.

Output data after running code provided by you.

<table border="0" cellpadding="0" cellspacing="0" width="522"><col style="mso-width-source:userset;mso-width-alt:4242;width:87pt" width="116"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="width:48pt" span="4" width="64"> <col style="mso-width-source:userset;mso-width-alt:2560;width:53pt" width="70"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:87pt" height="20" width="116">SYMBOL</td> <td style="width:60pt" width="80">TIMESTAMP</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">CONTRACTS</td> <td style="width:53pt" width="70">OPEN_INT</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-I</td> <td class="xl63" align="right">20110701</td> <td align="right">11299.9</td> <td align="right">11346.95</td> <td align="right">11212.25</td> <td align="right">39508</td> <td align="right">969625</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-II</td> <td class="xl63" align="right">20110701</td> <td align="right">11340.05</td> <td align="right">11354.75</td> <td align="right">11240</td> <td align="right">640</td> <td align="right">22625</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY-III</td> <td class="xl63" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-I</td> <td class="xl63" align="right">20110701</td> <td align="right">6668.5</td> <td align="right">6729.95</td> <td align="right">6660</td> <td align="right">81</td> <td align="right">15650</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-II</td> <td class="xl63" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT-III</td> <td class="xl63" align="right">20110701</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> </tr> </tbody></table>
Now i want to Multiply the Contract Column with Lot size Values Contained in Sheet1 of NSE Converter.xls

Sample Data of Lot Sizes. Actuallly the lot size is same irrespecitve of whether the symbol ends with '-I' , '-II" ,"-III"

<table border="0" cellpadding="0" cellspacing="0" width="128"><col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" height="20" width="64">SYMBOL </td> <td class="xl63" style="width:48pt" align="right" width="64">11-Jul</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">BANKNIFTY </td> <td align="right">25</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">MINIFTY </td> <td align="right">20</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NIFTY </td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">NFTYMCAP50</td> <td align="right">150</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">CNXIT </td> <td align="right">50</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">Symbol </td> <td class="xl63" align="right">11-Jul</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">3IINFOTECH</td> <td align="right">8000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">TRIVENI </td> <td align="right">2000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABAN </td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABB </td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABGSHIP </td> <td align="right">1000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ABIRLANUVO</td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ACC </td> <td align="right">250</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ADANIENT </td> <td align="right">500</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ADANIPOWER</td> <td align="right">2000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ALBK </td> <td align="right">1000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ALOKTEXT </td> <td align="right">10000</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">AMBUJACEM </td> <td align="right">2000</td> </tr> </tbody></table>
I used the following formula given by another member of this forum. This formula worked when the lot size data is copied to sheet2 of the original output. But i want to retain this lotsize data because i have to use it everyday with different fo.csv files Hence i copied it to the actual macro workbook i.e NSE Converter.xls and now the formula is not working. The Lot sizes change Everymonth and not everyday like the fo.csv file.

Hope i am clear now.

The reference given in the formula is correct. you can see where the NSE Converter.xls are used the same NSE Converter.xls is referred in Formula but the formula is not working though the macro code is opening the file referred in G2 of NSE Converter.xls

Code: 
ChDir "E:\Macros\Input"
Workbooks.Open Filename:="E:\Macros\Input\fo" & Format(Workbooks([COLOR=Red]"NSE Converter.xls"[/COLOR]).Sheets("Sheet1").Range("G2").Value, "ddmmmyyyy") & "bhav.csv", Origin:=xlWindows

Range("H2:H" & LR).Formula = "=F2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&[E:\Macros\[COLOR=Red]NSE Converter.xls[/COLOR]]Sheet1!$A$1:$A$226,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),[E:\Macros\[COLOR=Red]NSE Converter.xls[/COLOR]]Sheet1!$B$1:$B$226)"

Thank you
 
Upvote 0
Please ask in your other thread what the correct formula should be if Sheet1 is in a different workbook.
 
Upvote 0
Sir,

Working formula

=F2*LOOKUP(9.99999999999999E+307,SEARCH("#"&'[NSE Converter.xls]Sheet1'!$A$1:$A$226,"#"&SUBSTITUTE(TRIM(A2),CHAR(160),"")),'[NSE Converter.xls]Sheet1'!$B$1:$B$226)

But when i used this above formula in VBA i got Syntax error.

Kindly look at it.

Regards,

Zaska
 
Upvote 0
Try

Code: 
Range("H2:H" & LR).Formula = "=F2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&'[NSE Converter.xls]Sheet1'!$A$1:$A$226,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),'[NSE Converter.xls]Sheet1'!$B$1:$B$226)"
 
Upvote 0
Sir,

Thank you, formula worked in VBA but there is some confusion with the columns.." Close " column is missing from the Output and after implementing the formula i want to delete " Contract " Column too.


Original Data.

<table border="0" cellpadding="0" cellspacing="0" width="1027"><col style="mso-width-source:userset;mso-width-alt:3218;width:66pt" width="88"> <col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="mso-width-source:userset;mso-width-alt:2633;width:54pt" width="72"> <col style="width:48pt" span="12" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:66pt" height="20" width="88">INSTRUMENT</td> <td style="width:74pt" width="99">SYMBOL</td> <td style="width:54pt" width="72">EXPIRY_DT</td> <td style="width:48pt" width="64">STRIKE_PR</td> <td style="width:48pt" width="64">OPTION_TYP</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">SETTLE_PR</td> <td style="width:48pt" width="64">CONTRACTS</td> <td style="width:48pt" width="64">VAL_INLAKH</td> <td style="width:48pt" width="64">OPEN_INT</td> <td style="width:48pt" width="64">CHG_IN_OI</td> <td style="width:48pt" width="64">TIMESTAMP</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>BANKNIFTY</td> <td class="xl63" align="right">28-Jul-11</td> <td align="right">0</td> <td>XX</td> <td align="right">11299.9</td> <td align="right">11346.95</td> <td align="right">11212.25</td> <td align="right">11295.5</td> <td align="right">11295.5</td> <td align="right">39508</td> <td align="right">111327.1</td> <td align="right">969625</td> <td align="right">44900</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>BANKNIFTY</td> <td class="xl63" align="right">25-Aug-11</td> <td align="right">0</td> <td>XX</td> <td align="right">11340.05</td> <td align="right">11354.75</td> <td align="right">11240</td> <td align="right">11322.35</td> <td align="right">11322.35</td> <td align="right">640</td> <td align="right">1807.97</td> <td align="right">22625</td> <td align="right">5575</td> <td class="xl63" align="right">01-Jul-11</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">FUTIDX</td> <td>BANKNIFTY</td> <td class="xl63" align="right">29-Sep-11</td> <td align="right">0</td> <td>XX</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">11512.15</td> <td align="right">11519.8</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td align="right">0</td> <td class="xl63" align="right">01-Jul-11</td> </tr> </tbody></table>
Desired Output

<table border="0" cellpadding="0" cellspacing="0" width="651"><col style="mso-width-source:userset;mso-width-alt:3620;width:74pt" width="99"> <col style="mso-width-source:userset;mso-width-alt:2925;width:60pt" width="80"> <col style="mso-width-source:userset;mso-width-alt:2706; width:56pt" span="4" width="74"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:74pt" height="20" width="99">SYMBOL</td> <td class="xl67" style="border-left:none;width:60pt" width="80">TIMESTAMP</td> <td class="xl66" style="border-left:none;width:56pt" width="74">OPEN</td> <td class="xl66" style="border-left:none;width:56pt" width="74">HIGH</td> <td class="xl66" style="border-left:none;width:56pt" width="74">LOW</td> <td class="xl66" style="border-left:none;width:56pt" width="74">CLOSE</td> <td class="xl66" style="border-left:none;width:61pt" width="81">OPEN_INT</td> <td class="xl66" style="border-left:none;width:71pt" width="95">Formula Values</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">BANKNIFTY</td> <td class="xl67" style="border-top:none;border-left:none">20110701</td> <td class="xl68" style="border-top:none;border-left:none">11299.90 </td> <td class="xl68" style="border-top:none;border-left:none">11346.95 </td> <td class="xl68" style="border-top:none;border-left:none">11212.25 </td> <td class="xl68" style="border-top:none;border-left:none">11295.50 </td> <td class="xl66" style="border-top:none;border-left:none">969625</td> <td class="xl66" style="border-top:none;border-left:none">1975400</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">BANKNIFTY</td> <td class="xl67" style="border-top:none;border-left:none">20110701</td> <td class="xl68" style="border-top:none;border-left:none">11340.05 </td> <td class="xl68" style="border-top:none;border-left:none">11354.75 </td> <td class="xl68" style="border-top:none;border-left:none">11240.00 </td> <td class="xl68" style="border-top:none;border-left:none">11322.35 </td> <td class="xl66" style="border-top:none;border-left:none">22625</td> <td class="xl66" style="border-top:none;border-left:none">32000</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">BANKNIFTY</td> <td class="xl67" style="border-top:none;border-left:none">20110701</td> <td class="xl68" style="border-top:none;border-left:none">0.00 </td> <td class="xl68" style="border-top:none;border-left:none">0.00 </td> <td class="xl68" style="border-top:none;border-left:none">0.00 </td> <td class="xl68" style="border-top:none;border-left:none">11512.15 </td> <td class="xl66" style="border-top:none;border-left:none">0</td> <td class="xl66" style="border-top:none;border-left:none">0</td> </tr> </tbody></table>

Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
582
mumps
mumps
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,585
Messages
6,179,706
Members
452,939
Latest member
WCrawford

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