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

Luke M

Excel Ninja
@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.
 
Status
Not open for further replies.
Top