• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to solve this problem using VBA?

Status
Not open for further replies.
Dear Excel Experts,

I have an excel file named as "NIFTY21SEPFUT"were u will see the Date, Open, High, Low, Close, Vol columns. My idea is in a new excel file when i click on the command button and insert "NIFTY21SEPFUT" or any file it should convert the file in the format given in "NIFTYFUT.csv". I hope this can be done in VBA.. I'm not so expert in VBA so any experts do help me out ok

Thanking You All,

Regards,
Sonjoe

Attaching both the files for the reference.
 

Attachments

Marc L

Excel Ninja
According to your attachments you can rename your 'Insert File' button as 'Convert csv File' :​
Code:
Private Sub CommandButton1_Click()
    Dim T$, C$, F%, S$(), R&
        T = [C3].Text:  If T = "" Then Beep: Exit Sub
        C = Application.GetOpenFilename("Text Files,*.csv"):  If Not C Like "*.csv" Then Exit Sub
        F = FreeFile
        Open C For Input As #F
        S = Split(Input(LOF(F), #F), vbCrLf)
        Close #F
        If UBound(S) < 1 Then Beep: Exit Sub Else If Not S(1) Like "##-##-#### ##:##,*" Then Beep: Exit Sub
        C = Left(C, InStrRev(C, "\")) & T & ".csv"
        Open C For Output As #F
        Print #F, "Trading Symbol,Date,Time,Open,High,Low,Close/Price,Volume"
    For R = 1 To UBound(S) + (S(UBound(S)) = "")
        Print #F, T; ","; Left(S(R), 6); Mid(S(R), 9, 2); ","; Mid(S(R), 12, 5); ":00"; Mid(S(R), 17)
    Next
        Close #F
        Shell "Notepad " & C, 1
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Thank You Marc for ur prompt response. It worked well in the first instance, but after that when i backfilled data from the trading system it didn't work. The problem is when i backfill from the trading system i get a file like this which is attached along with this message named "NIFTY21SEPFUT.csv".

In the NIFTY.xlsm file which i have send u earlier in the yellow area when the user types the trading symbol format say for eg: NIFTYFUT, when the new CSV file is created the data should be in the following order: which is attached in NIFTYFUT.csv.

Trading SymbolDateTimeOpenHighLowClose/PriceVolume
NIFTYFUT
14-09-2021​
13:49:00​
17380​
17380.5​
17379.7​
17380​
4150​
NIFTYFUT
14-09-2021​
13:48:00​
17383.5​
17384.6​
17382.2​
17383.65​
5350​
NIFTYFUT
14-09-2021​
13:47:00​
17385.15​
17387.15​
17380.6​
17383.15​
12500​
NIFTYFUT
14-09-2021​
13:46:00​
17383.55​
17386.8​
17383.1​
17385.2​
2400​

Hope u got now and kindly change the cord accordingly.

Thanks & Regards,

Sonjoe
 

Attachments

Marc L

Excel Ninja
According to your initial post csv attachment, my VBA procedure creates exactly the same NIFTYFUT.csv text file as expected.​
If you need to overwrite the source csv text file rather than create a new NIFTYFUT.csv file then​
just remove the codeline C = Left(C, InStrRev(C, "\")) & T & ".csv" …​
As there is nothin' in your initial post about any order so this code was not designed for - as any Excel forum is not some mind readers forum ! -​
and can't be amended for such case but you can follow post #3 …​
As you already have created some threads on the same subject so this one may be seen as a duplicate.​
As a reminder for your next thread : How to get the Best Results
 
Dear Marc,

1. When i backfill data from the trading system it get a file which is attached along with this message "NIFTY21SEPFUT.csv
2. I'm also attaching the present excel file were i have deleted ur code mentioned above "Backfill to Ami.xlsm".
3. When i insert the NIFTY21SEPFUT file in the "Backfill to Ami.xlsm". its giving me a beep sound.
4. I don't want any format alterations to happen in the system generated file "NIFTY21SEPFUT.csv"
5. All the format alterations has to happen in a separate csv file named as per the yellow mark in the "Backfill to Ami.xlsm".

How come i send duplicates since i want a solution as per my requirement then only i can backfill in the Amibroker to study charts.
Awaiting for a solution for the problem.

Thanks & Regards,

Sonjoe
 

Attachments

Marc L

Excel Ninja
2. I'm also attaching the present excel file were i have deleted ur code mentioned above "Backfill to Ami.xlsm".
4. I don't want any format alterations to happen in the system generated file "NIFTY21SEPFUT.csv"
5. All the format alterations has to happen in a separate csv file named as per the yellow mark in the "Backfill to Ami.xlsm".
Just well reading my previous post you must have seen you do not ever need to delete this codeline !​
3. When i insert the NIFTY21SEPFUT file in the "Backfill to Ami.xlsm". its giving me a beep sound.
As this sound means the source text file does not match the expected format like the one in your initial attachment so it can't be converted !​
According to the initial requirement the post #5 code well creates the expected result text file so this thread is solved and now closed.​
When changing 'the rules' you must create a new thread, hoping it will finally be at the level of what any Excel forum expects for :​
a good enough elaboration - complete, crystal clear, nothing to guess - with an attachment well reflecting the before state​
like the expected result …​
 
Status
Not open for further replies.
Top