PDA

View Full Version : Solved: Allocations for CSV



Klartigue
04-25-2012, 08:20 AM
I have to put my allocations for one broker in a specific format. See attached document with my questions and comments.

Thanks

Tinbendr
04-25-2012, 08:32 AM
I little more info in the message body would be appreciated.

Do you want the sheet formatted to the transposed look, or do you want a CSV file with the data?

e.g.
666,6666666666,S,55000,93974CGH7,118.097,,,VS,2940,,

Klartigue
04-25-2012, 08:38 AM
Well I have to upload this file as .CSV so a CSV file with the data would be awesome. In column A, I would place the CSV Fields: And then in Column B, C, D, etc.. I would place the data for each allocation row (so from row 5 to lastrow). Data needs to be vertical so see below:

CSV FIELDS
OFC #
ACCT #
B/S
QUANTITY
SYMBOL
XPRICE
LIMIT
SETT
BKR
DLRAMT
DEALER
VSP
OCS/ACS
FI_DOLLAR_COMM

the limit, dlramt, vs, ocs/acs, and FI_Dollar_Comm will stay blank for all the allocations. SETT is the settlement date, which has to be in yyyy-mm-dd or yyyy/mm/dd format

Thanks for the help!

Tinbendr
04-25-2012, 10:55 AM
OK this is what I've come up with.

Option Explicit
Sub Export2CSV()
Dim aSheet As Worksheet
Dim LastRow As Long
Dim aCell As Range
Set aSheet = ActiveSheet
Open "Allocation.csv" For Output As #1
Print #1, "OFC #,ACCT #,B/S,QUANTITY,SYMBOL,XPRICE,LIMIT,SETT,BKR VS,DLRAMT,DEALER,VSP,OCS/ACS,FI_DOLLAR_COMM"
LastRow = aSheet.Range("A65536").End(xlUp).Row
For Each aCell In aSheet.Range("A5:A" & LastRow)
Print #1, Left(aCell.Offset(0, 6), 3) & "," & _
aCell.Offset(0, 6) & "," & _
aCell.Offset(0, 3) & "," & _
aCell.Offset(0, 7) & "," & _
aCell.Offset(0, 2) & "," & _
aCell.Offset(0, 4) & ",," & _
Format(aCell, "YYYY/MM/DD") & "," & _
aCell.Offset(0, 13) & ",," & _
aCell.Offset(0, 14) & ",,,"
Next
Close #1
End Sub



This will output all rows to a CSV file.

If you look at this with Notepad, you will see it is like the example I gave earlier.

Hope this helps.

Klartigue
04-25-2012, 10:59 AM
When I try that it highlights Offset (see red below) and says ambiguous name detected.



Option Explicit
Sub Export2CSV()
Dim aSheet As Worksheet
Dim LastRow As Long
Dim aCell As Range
Set aSheet = ActiveSheet
Open "Allocation.csv" For Output As #1
Print #1, "OFC #,ACCT #,B/S,QUANTITY,SYMBOL,XPRICE,LIMIT,SETT,BKR VS,DLRAMT,DEALER,VSP,OCS/ACS,FI_DOLLAR_COMM"
LastRow = aSheet.Range("A65536").End(xlUp).Row
For Each aCell In aSheet.Range("A5:A" & LastRow)
Print #1, Left(aCell.Offset(0, 6), 3) & "," & _
aCell.Offset(0, 6) & "," & _
aCell.Offset(0, 3) & "," & _
aCell.Offset(0, 7) & "," & _
aCell.Offset(0, 2) & "," & _
aCell.Offset(0, 4) & ",," & _
Format(aCell, "YYYY/MM/DD") & "," & _
aCell.Offset(0, 13) & ",," & _
aCell.Offset(0, 14) & ",,,"
Next
Close #1
End Sub

Tinbendr
04-25-2012, 02:20 PM
I copied your message code right off the page and it ran as described.

Klartigue
04-25-2012, 02:22 PM
what excel are you using? Im using Excel 2010

Klartigue
04-25-2012, 02:23 PM
I still get .offset as ambiguous name detected..hmm.

Klartigue
04-25-2012, 02:24 PM
Do i have to have that option explicit in there?

Tinbendr
04-25-2012, 03:20 PM
what excel are you using? Im using Excel 2010

I wrote it in 2007, but I've tried it since I'm home with 2010 and it runs fine.

That error usually means that there are two subs, variables, etc with the same name referenced in the code.

Try this instead.
Sub Export2CSV()
Dim aSheet As Worksheet
Dim LastRow As Long
Dim aCell As Range
Set aSheet = ActiveSheet
Open "Allocation.csv" For Output As #1
Print #1, "OFC #,ACCT #,B/S,QUANTITY,SYMBOL,XPRICE,LIMIT,SETT,BKR VS,DLRAMT,DEALER,VSP,OCS/ACS,FI_DOLLAR_COMM"
LastRow = aSheet.Range("A65536").End(xlUp).Row
For Each aCell In aSheet.Range("A5:A" & LastRow)
Print #1, Left(aCell.Offset(0, 6), 3) & ",";
Print #1, aCell.Offset(0, 6) & ",";
Print #1, aCell.Offset(0, 3) & ",";
Print #1, aCell.Offset(0, 7) & ",";
Print #1, aCell.Offset(0, 2) & ",";
Print #1, aCell.Offset(0, 4) & ",,";
Print #1, Format(aCell, "YYYY/MM/DD") & ",";
Print #1, aCell.Offset(0, 13) & ",,";
Print #1, aCell.Offset(0, 14) & ",,,"
Next
Close #1
End Sub

Bob Phillips
04-25-2012, 04:33 PM
Sub CreateCSV()
Dim wb As Workbook
Dim target As Worksheet
Dim source As Worksheet
Dim lastrow As Long
Dim nextrow As Long
Dim i As Long

Set source = ActiveSheet

Set wb = Workbooks.Add
Set target = wb.Worksheets(1)

With source

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
nextrow = 1
For i = 5 To lastrow

target.Cells(nextrow, "A").Resize(14) = Application.Transpose(Array( _
"OFC #", "ACCT #", "B/S", "QUANTITY", "SYMBOL", _
"XPRICE", "LIMIT", "ETT", "BKR", "DLRAMT", _
"DEALER", "VSP", "OCS/ACS", "FI_DOLLAR_COMM"))
target.Cells(nextrow, "B").Value = Left$(.Cells(i, "G").Value, 3)
target.Cells(nextrow + 1, "B").Value = .Cells(i, "G").Value
target.Cells(nextrow + 2, "B").Value = .Cells(i, "D").Value
target.Cells(nextrow + 3, "B").Value = .Cells(i, "H").Value
target.Cells(nextrow + 4, "B").Value = .Cells(i, "C").Value
target.Cells(nextrow + 5, "B").Value = .Cells(i, "E").Value
target.Cells(nextrow + 8, "B").Value = .Cells(i, "N").Value
target.Cells(nextrow + 10, "B").Value = .Cells(i, "O").Value

nextrow = nextrow + 14
Next i
End With

Application.DisplayAlerts = False
wb.SaveAs "C:\users\klartigue\desktop\klartigue", xlCSV
wb.Close
End Sub

Klartigue
04-26-2012, 06:26 AM
The above code works great! Although I see the fields start over starting in row 15, because they are doing the second allocation. Instead of having the fields start over, can I have the second allocation go in column C? See the red on the excel document. I would like to move those entries to column C starting in row 1 and build across rather than build down?

Klartigue
04-26-2012, 06:36 AM
David - I did try your code as well but i got an ambiguous error for the word "format" in this part: Print #1, Format(aCell, "YYYY/MM/DD") & ",";

Xld - your code works! Now if i can just get the second allocation to go to column c inside of going below the first allocation..hmm,!

Tinbendr
04-26-2012, 06:48 AM
I'm curious as a cat, now.

I've u/l the sample file I was working with. See if it errors on that.

Look in Module 1.

Bob Phillips
04-26-2012, 07:55 AM
Is this what you mean?
Sub CreateCSV()
Dim wb As Workbook
Dim target As Worksheet
Dim source As Worksheet
Dim lastrow As Long
Dim nextcol As Long
Dim i As Long

Set source = ActiveSheet

Set wb = Workbooks.Add
Set target = wb.Worksheets(1)

With source

target.Cells(1, "A").Resize(14) = Application.Transpose(Array( _
"OFC #", "ACCT #", "B/S", "QUANTITY", "SYMBOL", _
"XPRICE", "LIMIT", "ETT", "BKR", "DLRAMT", _
"DEALER", "VSP", "OCS/ACS", "FI_DOLLAR_COMM"))

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
nextcol = 2
For i = 5 To lastrow

target.Cells(1, nextcol).Value = Left$(.Cells(i, "G").Value, 3)
target.Cells(2, nextcol).Value = .Cells(i, "G").Value
target.Cells(3, nextcol).Value = .Cells(i, "D").Value
target.Cells(4, nextcol).Value = .Cells(i, "H").Value
target.Cells(5, nextcol).Value = .Cells(i, "C").Value
target.Cells(6, nextcol).Value = .Cells(i, "E").Value
target.Cells(9, nextcol).Value = .Cells(i, "N").Value
target.Cells(11, nextcol).Value = .Cells(i, "O").Value

nextcol = nextcol + 1
Next i
End With

Application.DisplayAlerts = False
wb.SaveAs "C:\users\bob\desktop\klartigue", xlCSV
wb.Close
End Sub

Klartigue
04-26-2012, 08:31 AM
Yes, that is exactly what I mean! Thank you soo much!

Klartigue
05-02-2012, 11:09 AM
The below code is what I am using right now. However, I found out it actually has to go horizontally, rather than vertically. So is there a way to just make everything horizontal, so column A is OFC #, column B is Acct #, column C is B/S, etc..and then have the allocation details below?

Sub CreateCSV()
Dim wb As Workbook
Dim target As Worksheet
Dim source As Worksheet
Dim lastrow As Long
Dim nextcol As Long
Dim i As Long

Set source = ActiveSheet

Set wb = Workbooks.Add
Set target = wb.Worksheets(1)

With source

target.Cells(1, "A").Resize(14) = Application.Transpose(Array( _
"OFC #", "ACCT #", "B/S", "QUANTITY", "SYMBOL", _
"XPRICE", "LIMIT", "ETT", "BKR", "DLRAMT", _
"DEALER", "VSP", "OCS/ACS", "FI_DOLLAR_COMM"))

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
nextcol = 2
For i = 5 To lastrow

target.Cells(1, nextcol).Value = Left$(.Cells(i, "G").Value, 3)
target.Cells(2, nextcol).Value = .Cells(i, "G").Value
target.Cells(3, nextcol).Value = .Cells(i, "D").Value
target.Cells(4, nextcol).Value = .Cells(i, "H").Value
target.Cells(5, nextcol).Value = .Cells(i, "C").Value
target.Cells(6, nextcol).Value = .Cells(i, "E").Value
target.Cells(9, nextcol).Value = .Cells(i, "N").Value
target.Cells(11, nextcol).Value = .Cells(i, "O").Value

nextcol = nextcol + 1
Next i
End With

End Sub

Klartigue
05-02-2012, 11:13 AM
Part of the above post..see the excel document attached.

Klartigue
05-03-2012, 07:18 AM
Any ideas on how to alter the above code so the results go across the columns rather than down columns?

Klartigue
05-03-2012, 09:34 AM
I just ended up doing it like this to get things to go in rows rather than columns:


Sub MSSVCSV()
Call LabelColumns
Call Sheet1
Call Pasteaccountnumbers
Call ExpandColumns
Call Macro3
Call MoveData
Call Dealer
Call SaveMSSBCSV
End Sub
Sub LabelColumns()
'
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
ActiveCell.FormulaR1C1 = "OFC #"
Range("B1").Select
ActiveCell.FormulaR1C1 = "ACCT #"
Range("C1").Select
ActiveCell.FormulaR1C1 = "B/S"
Range("D1").Select
ActiveCell.FormulaR1C1 = "QUANTITY"
Range("E1").Select
ActiveCell.FormulaR1C1 = "SYMBOL"
Range("F1").Select
ActiveCell.FormulaR1C1 = "XPRICE"
Range("G1").Select
ActiveCell.FormulaR1C1 = "LIMIT"
Range("H1").Select
ActiveCell.FormulaR1C1 = "SETT"
Range("I1").Select
ActiveCell.FormulaR1C1 = "BKR"
Range("J1").Select
ActiveCell.FormulaR1C1 = "DLRAMT"
Range("K1").Select
ActiveCell.FormulaR1C1 = "DEALER"
Range("L1").Select
ActiveCell.FormulaR1C1 = "VSP"
Range("M1").Select
ActiveCell.FormulaR1C1 = "OCS/ACS"
Range("N1").Select
ActiveCell.FormulaR1C1 = "FI_DOLLAR_COMM"
Range("A2").Select
End Sub
Sub Sheet1()
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
End Sub
Sub Pasteaccountnumbers()
'
Range("G5:G36").Select
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("B2").Select
ActiveSheet.Paste

End Sub
Sub ExpandColumns()
'
Columns("B:B").Select
Columns("B:B").EntireColumn.Autofit
Selection.ColumnWidth = 13
End Sub
Sub Macro3()
'
Dim lastrow As Long
Dim i As Long
With ActiveSheet

lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 2 To lastrow

.Cells(i, "A").Select
ActiveCell.FormulaR1C1 = "=LEFT(Sheet1!R[3]C[6],3)"

Next i
End With

End Sub
Sub MoveData()
'
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("D5:D39").Select
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("H5:H39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("D2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("C5:C39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("E2").Select
ActiveSheet.Paste
Columns("E:E").EntireColumn.Autofit
Columns("D:D").ColumnWidth = 10.57
Columns("E:E").ColumnWidth = 10.86
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("E5:E39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("F2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("A5:A39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("H2").Select
ActiveSheet.Paste
Columns("H:H").ColumnWidth = 11.86
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("N5:N39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("I2").Select
ActiveSheet.Paste
End Sub
Sub Dealer()
'
Sheets("Sheet1").Select
Application.Run "BLPLinkReset"
Range("O5:O39").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Application.Run "BLPLinkReset"
Range("K2").Select
ActiveSheet.Paste
End Sub
Sub SaveMSSBCSV()
ChDir "G:\Katherine Lartigue\Allocations\MSSB CSV UPLOAD"
ActiveWorkbook.Saveas Filename:= _
"G:\Katherine Lartigue\Allocations\MSSB CSV UPLOAD\Avalon Trades.csv", _
FileFormat:=xlCSV, CreateBackup:=False

End Sub