Solved

Add column and change Exp column format

Posted on 2011-03-12
19
382 Views
Last Modified: 2012-05-11
The output tab is the result of the Macro below.  Unfortunately, the Exp column shown is not reflecting the Exp month and year correctly because it was originally a CSV file.  I need the macro used to create the output tab expanded to correct the Exp column to show an abbreviated month and four(4) digit year (i.e. Mar 2011).  I also need the addition of another empty column entitled Position.  Assistance is greatly appreciated.
Option Explicit

Public Sub CopyTradeHistory()
  Dim wks As Worksheet
  Dim rngSrc As Range
  Dim strSheetname As String
  Dim lastrow As Long
  
  On Error Resume Next
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
  Set wks = ActiveSheet
  strSheetname = wks.Name
  Set rngSrc = wks.Cells.Find("Account Trade History")
  Set rngSrc = wks.Range(rngSrc, rngSrc.End(xlDown).End(xlDown).Offset(0, 11))
  ActiveWorkbook.Worksheets(strSheetname & "_output").Delete
  Set wks = ActiveWorkbook.Worksheets.Add
  wks.Name = strSheetname & "_output"

  rngSrc.Copy wks.Range("A1")
  wks.Columns(3).Insert
  wks.Columns(5).Insert
  wks.Cells(2, 3).Value = "Strategy#"
  wks.Cells(2, 4).Value = "Strategy"
  wks.Cells(2, 5).Value = "Leg#"
  wks.Rows("1:2").Font.Bold = True
  lastrow = wks.Range("a" & 64000).End(xlUp).Row
  wks.Range("E3:E" & lastrow).Formula = "=IF(OR(H3<>H2,E2=""leg2""),""Leg1"",""Leg2"")"
  wks.Range("E3:E" & lastrow).Value = wks.Range("E3:E" & lastrow).Value
  wks.Range("C3:C" & lastrow).Formula = "=counta(B3:B$3)"
  wks.Range("C3:C" & lastrow).NumberFormat = "General"
  wks.Range("C3:C" & lastrow).Value = wks.Range("C3:C" & lastrow).Value
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

Open in new window

TradeHist-2.3a.xlsm
0
Comment
Question by:rtod2
  • 11
  • 8
19 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35119779
This?
Option Explicit

Public Sub CopyTradeHistory()
  Dim wks As Worksheet
  Dim rngSrc As Range
  Dim strSheetname As String
  Dim lastrow As Long
  
  On Error Resume Next
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
  Set wks = ActiveSheet
  strSheetname = wks.Name
  Set rngSrc = wks.Cells.Find("Account Trade History")
  Set rngSrc = wks.Range(rngSrc, rngSrc.End(xlDown).End(xlDown).Offset(0, 11))
  ActiveWorkbook.Worksheets(strSheetname & "_output").Delete
  Set wks = ActiveWorkbook.Worksheets.Add
  wks.Name = strSheetname & "_output"

  rngSrc.Copy wks.Range("A1")
  wks.Columns(3).Insert
  wks.Columns(5).Insert
  wks.Cells(2, 3).Value = "Strategy#"
  wks.Cells(2, 4).Value = "Strategy"
  wks.Cells(2, 5).Value = "Leg#"
  wks.Cells(2, 15).Value = "Position"
  wks.Rows("1:2").Font.Bold = True
  lastrow = wks.Range("a" & 64000).End(xlUp).Row
  wks.Range("E3:E" & lastrow).Formula = "=IF(OR(H3<>H2,E2=""leg2""),""Leg1"",""Leg2"")"
  wks.Range("E3:E" & lastrow).Value = wks.Range("E3:E" & lastrow).Value
  wks.Range("C3:C" & lastrow).Formula = "=counta(B3:B$3)"
  wks.Range("C3:C" & lastrow).NumberFormat = "General"
  wks.Range("C3:C" & lastrow).Value = wks.Range("C3:C" & lastrow).Value
  wks.Range("I3:I" & lastrow).NumberFormat = "mmm-yyyy"
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

Open in new window

0
 

Author Comment

by:rtod2
ID: 35119781
The Position# column should appear just before the Strategy# column.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35119797
Option Explicit

Public Sub CopyTradeHistory()
  Dim wks As Worksheet
  Dim rngSrc As Range
  Dim strSheetname As String
  Dim lastrow As Long
 
  On Error Resume Next
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
 
  Set wks = ActiveSheet
  strSheetname = wks.Name
  Set rngSrc = wks.Cells.Find("Account Trade History")
  Set rngSrc = wks.Range(rngSrc, rngSrc.End(xlDown).End(xlDown).Offset(0, 11))
  ActiveWorkbook.Worksheets(strSheetname & "_output").Delete
  Set wks = ActiveWorkbook.Worksheets.Add
  wks.Name = strSheetname & "_output"

  rngSrc.Copy wks.Range("A1")
  wks.Columns(3).Insert
  wks.Columns(5).Insert
  wks.Cells(2, 3).Value = "Strategy#"
  wks.Cells(2, 4).Value = "Strategy"
  wks.Cells(2, 5).Value = "Leg#"
  wks.Rows("1:2").Font.Bold = True
  lastrow = wks.Range("a" & 64000).End(xlUp).Row
  wks.Range("E3:E" & lastrow).Formula = "=IF(OR(H3<>H2,E2=""leg2""),""Leg1"",""Leg2"")"
  wks.Range("E3:E" & lastrow).Value = wks.Range("E3:E" & lastrow).Value
  wks.Range("C3:C" & lastrow).Formula = "=counta(B3:B$3)"
  wks.Range("C3:C" & lastrow).NumberFormat = "General"
  wks.Range("C3:C" & lastrow).Value = wks.Range("C3:C" & lastrow).Value
  wks.Range("I3:I" & lastrow).NumberFormat = "mmm-yyyy"
  wks.Columns(3).Insert
  wks.Cells(2, 3).Value = "Position"
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub
0
 

Author Comment

by:rtod2
ID: 35119800
All the cells in the Exp column should be four (4) digit years and there should not be a dash, only a space.  The Position column should say Position# and should appear just before the Strategy# column.  My mistake with the column name Position#.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35119804

Option Explicit

Public Sub CopyTradeHistory()
  Dim wks As Worksheet
  Dim rngSrc As Range
  Dim strSheetname As String
  Dim lastrow As Long
  
  On Error Resume Next
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
  Set wks = ActiveSheet
  strSheetname = wks.Name
  Set rngSrc = wks.Cells.Find("Account Trade History")
  Set rngSrc = wks.Range(rngSrc, rngSrc.End(xlDown).End(xlDown).Offset(0, 11))
  ActiveWorkbook.Worksheets(strSheetname & "_output").Delete
  Set wks = ActiveWorkbook.Worksheets.Add
  wks.Name = strSheetname & "_output"

  rngSrc.Copy wks.Range("A1")
  wks.Columns(3).Insert
  wks.Columns(5).Insert
  wks.Cells(2, 3).Value = "Strategy#"
  wks.Cells(2, 4).Value = "Strategy"
  wks.Cells(2, 5).Value = "Leg#"
  wks.Rows("1:2").Font.Bold = True
  lastrow = wks.Range("a" & 64000).End(xlUp).Row
  wks.Range("E3:E" & lastrow).Formula = "=IF(OR(H3<>H2,E2=""leg2""),""Leg1"",""Leg2"")"
  wks.Range("E3:E" & lastrow).Value = wks.Range("E3:E" & lastrow).Value
  wks.Range("C3:C" & lastrow).Formula = "=counta(B3:B$3)"
  wks.Range("C3:C" & lastrow).NumberFormat = "General"
  wks.Range("C3:C" & lastrow).Value = wks.Range("C3:C" & lastrow).Value
  wks.Range("I3:I" & lastrow).NumberFormat = "mmm yyyy"
  wks.Columns(3).Insert
  wks.Cells(2, 3).Value = "Position"
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

Open in new window

0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 35119808

Option Explicit

Public Sub CopyTradeHistory()
  Dim wks As Worksheet
  Dim rngSrc As Range
  Dim strSheetname As String
  Dim lastrow As Long
  
  On Error Resume Next
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  
  Set wks = ActiveSheet
  strSheetname = wks.Name
  Set rngSrc = wks.Cells.Find("Account Trade History")
  Set rngSrc = wks.Range(rngSrc, rngSrc.End(xlDown).End(xlDown).Offset(0, 11))
  ActiveWorkbook.Worksheets(strSheetname & "_output").Delete
  Set wks = ActiveWorkbook.Worksheets.Add
  wks.Name = strSheetname & "_output"

  rngSrc.Copy wks.Range("A1")
  wks.Columns(3).Insert
  wks.Columns(5).Insert
  wks.Cells(2, 3).Value = "Strategy#"
  wks.Cells(2, 4).Value = "Strategy"
  wks.Cells(2, 5).Value = "Leg#"
  wks.Rows("1:2").Font.Bold = True
  lastrow = wks.Range("a" & 64000).End(xlUp).Row
  wks.Range("E3:E" & lastrow).Formula = "=IF(OR(H3<>H2,E2=""leg2""),""Leg1"",""Leg2"")"
  wks.Range("E3:E" & lastrow).Value = wks.Range("E3:E" & lastrow).Value
  wks.Range("C3:C" & lastrow).Formula = "=counta(B3:B$3)"
  wks.Range("C3:C" & lastrow).NumberFormat = "General"
  wks.Range("C3:C" & lastrow).Value = wks.Range("C3:C" & lastrow).Value
  wks.Range("I3:I" & lastrow).NumberFormat = "mmm yyyy"
  wks.Columns(3).Insert
  wks.Cells(2, 3).Value = "Position#"
  Application.CutCopyMode = False
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
End Sub

Open in new window

0
 

Author Comment

by:rtod2
ID: 35119833
Almost.  A few of the expiration months still do not show a four (4) digit year.  I have not yet figured out why some appear to be showing a date.  I think just eliminate an actual date where there is one and use the month and year only.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35119839
That is because the date in the source sheet is entered in a format which excel does not recognize as a date
0
 

Author Comment

by:rtod2
ID: 35119852
Yep, but it is.  Should be processed as Mo Year.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35119858
Unless the entry is recognized by excel as a date it cannot be processed.
0
 

Author Comment

by:rtod2
ID: 35119868
That's a show stopper for sure.  Not sure how to handle that because the next question involves combining those columns (Symbol, Exp, Strike, Type) horizontally.
0
 

Author Comment

by:rtod2
ID: 35119873
Those column values should show in a single cell as a cohesive unit on which no calculations or sorting will occur.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35119877
The proper way is to go back to the source data and correct the faulty data.

If there are hundreds of data to be corrected then maybe come correction pattern can be worked out.
0
 

Author Comment

by:rtod2
ID: 35119895
OK, then we can leave it I guess.  The source is what it is.  I am a user of the product.  I have uploaded a CSV file just as it would come out of that product.  Is that what you mean?  Thank you for helping with this.  I'll ask a new question about the combining of the columns on the next post.
2011-03-13-AccountStatement.csv
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35119914
If you look at lines 140 and 193 the Sep5 seems to be a pattern or have some meaning. If this is the case then a formula can be worked out to convert sep5 to sep
0
 

Author Comment

by:rtod2
ID: 35119947
I see.  Thank you for pointing that out.  It is a Quarterly which means it should have a specific date instead of just a month and year.  So it would be OK to add the extra two (2) digits to the year, a space between the month and the day, and then combine the three (3) columns Exp, Strike, Type leaving the symbol column alone.  This should give me enough representative data to work with when trying to define a position number for each type position.  If needed, I will gladly pose the question about combining the three (3) columns by itself in another question.
0
 

Author Comment

by:rtod2
ID: 35120031
Once the Exp issue is corrected as much as it can be with the current data; below is a list of questions that will come next.  They are only included here as a placeholder.  I will check back in the morning and follow-up with a new question.  Thank you very much for your help sir.

COMBINE - Combine only Exp, Strike, Type columns with only a single space between each. (i.e. Mar 2010 45 Call)
WIDTH - Width of columns shoud adjust to contents.
FREEZE - Freeze Column Header Rows
NOTES - The Account Order History (filled and expired items only) precedes the same item in the Account Trade History.  What I need are the notes from each of the filled and expired items.  The term 'default' in column A of the Account Trade History should be removed but the Order History Notes should appear in the last column instead wrapped if needed so that they will print when the sheet is printed to a PDF.
0
 

Author Closing Comment

by:rtod2
ID: 35122150
Outstaning!
0
 

Author Comment

by:rtod2
ID: 35122191
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now