Solved

FO Formatting

Posted on 2015-01-12
19
146 Views
Last Modified: 2015-01-13
Hi Experts,

I have on.csv  file in which I have to change data programmatically. Please help me out with VBA.
Here are the steps.

01.

In attached .csv file Find "NIFTY" in column B with criteria "XX" in column E. there are 3 matches with this criteria.Finding Dates

02.

Now we have 3 dates in column C as per above criteria i.e. "29-Jan-15" "26-Feb-15" "26-Mar-15
". Put text values to this dates "I" "II" "III" in Oldest to newest. So 29-Jan-15 = I , 26-Feb-15 = II & 26-Mar-15 = III. apply this values to respective dates in all column C.Putting Values To Date

03.

Delete all rows which doesn't match values "I" "II" "III" in column C
Need to execute this from other WB & above file location is "D:\AmiBroker Data\NSE\FO\". there are numbers of files.
Need to Execute for all files but not now as this is part of process there are follow up questions.

See attached

Thanks
fo06JAN2015bhav.csv
After.csv
0
Comment
Question by:itjockey
  • 11
  • 8
19 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40544162
This should be for all files in the directory ?? Sorry maybe you mentioned it but faster to ask.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40544179
An other question. In the example you only had 3 dates and applied I, II, III would it happens so that:

1) You can have several rows of the same date ? like several I or II ?
2) You can have more than 3 dates ? like 5 or 6 dates ? if yes then how do we name them roman ? I, II, III, IV, V etc ... ?
3) Can we name them say A, B C etc ... instead of roman ?
4) You want to delete all rows that does not meet these new naming including the header or keeping the header ?
 
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40544180
yes Sir for all files but still something is still left which is for next follow-up question. So say this is part A next question part B. part A+B formatting for all files in directory.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40544185
pls see my last comment but ignore only point 1) as I just realized that you want to rename all dates like 29-Jan-15 to I and not only this record !

Waiting for answer on remaining points.

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40544189
Last post is in reply of your second last post.

1) Yes Same dates need to assign I or II or III.
2) There will be only 3 dates with match criteria i.e. NIFTY XX.
3) If in future it creates problem then 1 2 3 will be fine.
4) Sorry my mistake - Keep heading.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40544200
No problem if only 3 different dates then we can use I, II, III but if you have variable dates like 10 or 15 different dates then it is a 'P A I N' to start counting in roman !!! :)

gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40544206
yes but I II III will be in older to newer date i.e. I will be oldest date and III will be newest date.

Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40544210
Yes sure no problem. Older to newest do you have any problem if the data is sorted by I, II, III ??? Like all I then all II etc...

Last but no least, we override existing file right ? like we save the new changes and replace the existing file ?
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40544225
1 min Sir.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 8

Author Comment

by:itjockey
ID: 40544294
Ultimately I need to create .txt file after deleting actual file.
in attached look in the column Q there is formula which combine cell values. so above procedure then formula then copy column Q and create .txt file & delete original file. this is my next follow up question. .txt file name as actual file name.

See attached
After.xlsx
Final-File.txt
0
 
LVL 8

Author Comment

by:itjockey
ID: 40544306
Sorry for delay as I created formula and save in .csv format so after saving formula gone and displayed all values in column Q as text. so I had created again and save as .xlsx file.


Thanks
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40544967
Sorry I do not appreciate when you come up with something new just in the middle of a question. Sorry too late as I already developed the macro as per your initial request.

When I came to compare my result based on the file you posted fo06JAN2015bhav.csv which have 86 rows I noticed that your After.csv file have: 26470 rows !!! how can 86 rows become 26470 you must have posted the wrong file !!!

Pls checkout the fo06JAN2015bhav.csv file you posted and make out a corresponding After.csv and posted so I can make sure all is fine and then I will post my solution.

We can look at other request in subsequent questions if you like.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40545159
See attached fo06jan2015(1).xlsx explained how come 26469 rows.

Thanks
fo06JAN2015bhav--1-.xlsx
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40545336
ok it seems the file I first downloaded got truncated and I only got 86 rows. Now I see the file is BIG !!! but nevertheless my routine is fine as I come up with the same results as you.

Here is how to incorporate directly in your Production workbook. Please follow carefully the instructions as the solution takes into consideration trace and Audit and cannot be posted separately but with the scoop of your workbook.

1) Make a copy of your latest workbook and give it a new name.
2) Open VBA and create a new module and change its name to aStartFO
3) Paste the below function in this new module

Function FormatingFO(sType As String, sFolder As String) As String
On Error GoTo ErrHandler

Dim WB As Workbook
Dim WS As Worksheet
Dim WSAudit As Worksheet
Dim WSMain As Worksheet
Dim MaxRow As Long, MaxCol As Long, MaxRowA As Long, I As Long, J As Long
Dim lUns As Long, lRows As Long
Dim Rng As Range, cRow As Range
Dim sFile As String, sDirName As String, sDate As String
Dim dDates
Dim colFiles As New Collection
Dim vFile As Variant
    
'---> Set Variables
Set WSAudit = Sheets("Audit")
MaxRowA = WSAudit.Range("A" & WSAudit.Rows.Count).End(xlUp).Row
If MaxRowA = 1 Then MaxRowA = MaxRowA + 1
Set WSMain = ActiveSheet

'---> Get the Recursive Files and folders
RecursiveDir colFiles, sFolder, "*.csv", True

For Each vFile In colFiles
           
    '---> Get full name
    sFile = Dir(vFile)
    sDirName = Mid(vFile, 1, InStrRev(vFile, "\"))
    
    '---> Update Trace
    If Not bTrace Then
        WSMain.Cells(14, "A").EntireRow.Insert
        WSMain.Cells(14, "A") = sType
        WSMain.Cells(14, "B") = sDirName
        WSMain.Cells(14, "C") = sFile
    End If
    
    '---> Disable Events
    With Application
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
    
    '---> Open workbook and affect variables
    Set WB = Workbooks.Open(vFile)
    Set WS = WB.ActiveSheet
    MaxRow = WS.UsedRange.Rows.Count
    MaxCol = WS.UsedRange.Columns.Count
    
    '---> Find Criteria Dates
    WS.UsedRange.AutoFilter field:=2, Criteria1:="NIFTY"
    WS.UsedRange.AutoFilter field:=5, Criteria1:="XX"
    
    For I = 2 To MaxRow
        If WS.Range("A" & I).EntireRow.Hidden = False Then
            If sdates <> "" Then sdates = sdates & ";"
            sdates = sdates & DateValue(WS.Cells(I, "C"))
            J = J + 1
        End If
    Next I
            
    dDates = Split(sdates, ";")
    sdates = ""
    
    '---> Loop Thru all the Criteria
    For I = LBound(dDates) To UBound(dDates)
        If WS.AutoFilterMode = True Then WS.ShowAllData
        
        '---> Apply Filter
        WS.UsedRange.AutoFilter field:=3, Criteria1:=">=" & dDates(I), Operator:=xlAnd, Criteria2:="<=" & dDates(I)
        
        '---> Set the Current Range
        On Error Resume Next
        Set Rng = WS.Range(WS.Range("A2"), WS.Cells(MaxRow, MaxCol)).EntireRow.SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        
        '---> Change Date in Col C by coresponding letter
        Select Case I
            Case 0
                sDate = "I"
            Case 1
                sDate = "II"
            Case 2
                sDate = "III"
            Case Else
                sDate = "I" & Str(I)
        End Select
        
        lUns = 0
        For J = 2 To MaxRow
            If WS.Range("A" & J).EntireRow.Hidden = False Then
                WS.Range("C" & J) = sDate
                lUns = lUns + 1
            End If
        Next J
        
        '---> Register the record found in Audit
        If Not bAudit Then
            WSAudit.Cells(MaxRowA, "A") = Now
            WSAudit.Cells(MaxRowA, "B") = sFile
            WSAudit.Cells(MaxRowA, "C") = dDates(I) & " " & sDate
            WSAudit.Cells(MaxRowA, "D") = Rng.Address
            MaxRowA = MaxRowA + 1
        End If
        
        '---> Update Trace
        If Not bTrace Then
            WSMain.Cells(14, "A").EntireRow.Insert
            WSMain.Cells(14, "A") = sType
            WSMain.Cells(14, "B") = sDirName
            WSMain.Cells(14, "C") = sFile
            WSMain.Cells(14, "D") = sDate
            WSMain.Cells(14, "E") = lUns
            WSMain.Cells(14, "F") = "Formated"
        End If
        DoEvents
    Next I
    
    '---> Delete All rows that have a date in Col C
    For I = MaxRow To 2 Step -1
        If IsDate(WS.Cells(I, "C")) Then
            WS.Range("C" & I).EntireRow.Delete
            lRows = lRows + 1
        End If
    Next I
    
    If Not bAudit Then
        WSAudit.Cells(MaxRowA, "A") = Now
        WSAudit.Cells(MaxRowA, "B") = sFile
        WSAudit.Cells(MaxRowA, "C") = Deleted & lRows & " Rows"
        MaxRowA = MaxRowA + 1
    End If
        

    '---> Update Trace Status
    If Not bTrace Then
        WSMain.Cells(14, "A").EntireRow.Insert
        WSMain.Cells(14, "A") = sType
        WSMain.Cells(14, "B") = sDirName
        WSMain.Cells(14, "C") = sFile
        WSMain.Cells(14, "E") = lRows
        WSMain.Cells(14, "F") = "Deleted"
    End If
    
    '---> Remove Filtering
    WS.ShowAllData
    WS.AutoFilterMode = False
    'WS.UsedRange.EntireColumn.AutoFit
        
    '---> Save workbook
    WB.Close savechanges:=True
    
    '---> reset Variables
    Set WS = Nothing
    Set WB = Nothing
    Set Rng = Nothing
    lUns = 0
    lRows = 0
    'MaxRowM = MaxRowM + 1
    
    '---> Enable Events
    With Application
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    
Next vFile

'---> fix Layout
If Not bAudit Then
    WSAudit.UsedRange.EntireColumn.AutoFit
End If

'---> Set Flag to complete successful and exit
FormatingFO = ""
Exit Function

ErrHandler:
MsgBox (Error(Err))
FormatingFO = Error(Err)
Resume
On Error GoTo 0

End Function

Open in new window


4) SAVE your workbook
5) Doubleclick on sheet Main and select the commandbutton4
6) Add these couple of lines after the last line of code

'---> Delete Files in Directory
'DeleteFiles gstDestinationFolder

'---> Formating FO
FormatingFO CommandButton4.Caption, gstDestinationFolder

MsgBox ("Process Future & Options Completed")

Open in new window


7) SAVE the workbook.
8) Close the workbook.
9) Now to try it you should put this fo06JAN2015bhav--1-.xlsx file in the directory
D:\AmiBroker Data\NSE\F&O\

10) Try putting trace on so you can see the trace and results
11) Load the file and try it by pressing on the fourth button Furture & Options.

Let me know
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40545414
Worked Perfect. May I ask Follow Up New Question?
0
 
LVL 8

Author Closing Comment

by:itjockey
ID: 40545416
Perfect.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40546042
Yes pls go ahead.
gowflow
0
 
LVL 8

Author Comment

by:itjockey
ID: 40546102
Posting link over here in 10 min.

Thanks
0
 
LVL 8

Author Comment

by:itjockey
ID: 40546128
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Outlook Free & Paid Tools
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

757 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

16 Experts available now in Live!

Get 1:1 Help Now