How to solve this problem using VBA?

[SONJOE JOSEPH]

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.

 

[Marc L]

Hi !​

I have an excel file named as […]

As your attachment contains only text files without any Excel file …​

​

 

[Marc L]

I'm not so expert in VBA

As a reminder any beginner can activate the Macro Recorder and operate manually for starters …​

​

 

[SONJOE JOSEPH]

The file is attached waiting for the solution...Thank you for responding

 

[Marc L]

According to your attachments you can rename your 'Insert File' button as 'Convert csv File' :​

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 !​

​

 

[SONJOE JOSEPH]

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 Symbol	Date	Time	Open	High	Low	Close/Price	Volume
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

 

[Marc L]

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​

​

 

[SONJOE JOSEPH]

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

 

[Marc L]

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 …​

​

 

[Luke M]

@Marc L
I can understand your frustration with the changing requirements and multiple threads. However, I would ask you to tone done some of the sarcasm. Some of your above posts can come across as pretty harsh responses. I know that all of use can have off/frustrating days, but we need to assume the best intentions from other posters. Criticizing someone and then closing the thread in same action was not seen as helpful.

@SONJOE JOSEPH
It looks like per the original request, Marc was able to answer your question. However, you then discovered that you needed to add/change requirements, which causes frustration for those (such as Marc) who spent time trying to solve the original problem. No one likes to feel like they wasted their (volunteer) time. As this thread led to frustration on both your parts, I agree with Marc that you should (and it looks like you already did?) start a new thread with the requirements you need.

If either of you have additional comments you'd like to make, please send me a private message, and I will be happy to arbitrate.