1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Can a manual work which i do regularly be automated using excel macros or plugin type thing?

Discussion in 'VBA Macros' started by SONJOE JOSEPH, Jan 17, 2019.

  1. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    Dear All Excel Experts,

    I would like to automate a process were from my trading terminal i used to backfill Intra day EOD data on a daily basis. Currently i do it manually which is a tedious process the steps are listed below:-

    1. I used to download the data which once fully downloaded will be a CSV file. The default file name will be the scrip name i selected say for eg:- "NIFTY19JANFUT". The first thing i used to do is to rename this file as "NTY19JANFUT" which will be saved as CSV file.

    2. Once the file is opened you can see the headings Date, Nifty19janFut O, Nifty19janFut H, Nifty19janFut L, Nifty19janFut C & Nifty19janFut V. First i need to rename the headings as Time, Open, High, Low, Close & Volume. Screenshot attached below ok

    3. Before the Date column i will be inserting a blank column and this column heading will be "Trading Symbol". Then in cell A2 i used to type "NTY19JANFUT" and then i used to copy this trading symbol down till the end of the data

    4. Once all the 3 steps are done i used to save the CSV file in the same file name as "NTY19JANFUT".

    Can the above process be automated in excel using macros or a plugin type thing. Please help me friends.

    Thanking You and waiting to hear from all excel experts.

    With Regards,

    Sonjoe Joseph

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    4,805
    SONJOE JOSEPH
    It seems to be possible ...
    but with photo
    ... it'll be a challenge.

    >>
    Upload a sample file as raw file
    and
    a sample file, which You have done Your daily duties.
  3. ExcelSur

    ExcelSur New Member

    Messages:
    13


    Power Query can do these steps using the Append feature in Power Query

    https://www.powerquery.training/book-files/
  4. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    Good Morning Vletm,

    Here sending the files as attachment.

    1.I selected the script NIFTY19JANFUT. Once i open the chart for the script there is an option to download the data in excel. The downloaded file from my trading terminal the default file name will be the selected script name in this case it was NIFTY19JANFUT.csv(File Attached) This file name has to to renamed NTY19JANFUT.csv

    2. Once u open the NIFTY19JANFUT.CSV file u will find default headings like Date, NIFTY19JANFUT O, NIFTY19JANFUT H, NIFTY19JANFUT L, NIFTY19JANFUT C, NIFTY19JANFUT V. Before the date i will be adding a blank column the heading for this column will be Trading Symbol, Date, Open, High, Low, Close/Price, Volume. NTY19JANFUT.CSV (File Attached). Then in cell A2 i will be typing NTY19JANFUT and this will be copied till the end of the data.

    3. Now the next process in the Date header i would like to separate the Date & time in different columns. For this what i do is before the "Open" header i insert a column . Now i will select the whole "Date" column then in excel will go to the Data ->Text to Columns ->Fixed width radio button and click Next. Then again click Next -> again Next ->Make sure the "General" tab is selected and then "Finish". This column header will be named as "Time". So now the time is seperated from the date column. (File attached)

    4. Now we need to format the date column dd-mm-yyyy and finally this file has to be saved.

    5. Now for backfilling this CSV data in the amibroker chart. First this CSV files are in the path Computer -> Local Disk (D) -> Pi -> Exported. My idea is the user can select which all CSV files he would like to import into the Amibroker chart. (Screen shot attached)

    6. Finally my database for Amibroker is in the path C:\Program Files(x86)Amibroker\NSE (Screenshot attached)

    Hope u will help me in automating the whole process.

    Thanking You,

    With Regards,

    Sonjoe Joseph

    Attached Files:

  5. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
  6. vletm

    vletm Excel Ninja

    Messages:
    4,805
    SONJOE JOSEPH
    1) You're writing something which cannot see ...
    2) okay
    3) My way
    4) That didn't happen with Your files!
    5) There were NO any chart in Your files
    and I didn't start to do it. It would be possible too.
    6) Skipped

    > Usage:
    1) Open SJ.xlsb -file
    2) Press [ Select File ]-button ... and select file
    3) ... wait until You'll get 'Done'

    > Questions?

    Attached Files:

    • SJ.xlsb
      File size:
      24.2 KB
      Views:
      4
  7. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    Hello Vletm,

    Checked ur file and a few corrections has to be done.

    1. The selected file once the process is done is still showing the same file name as "NIFTY19JANFUT.CSV". This file has to be renamed to "NTY19JANFUT.CSV" or once the process is done it has to create a new CSV file in the name "NTY19JANFUT.CSV".

    2. The headers all are fine except the Volume its wrong. Its showing as "NIFTY19JANFUT V" its should be "Volume".

    3. The date format has to be in "dd/mm/yy". Now its showing as "dd/mm/yyyy 00:00. I just want to get rid of the 00:00 ok

    4. Now i need to backfill this data into my Amibroker charting software. How this can be done. My idea is there should be a "Backfill"button. Once the "Backfill button is clicked its has to automatically open the Amibroker charting software and do the backfill process so that the data is updated. Do let me know what else u want to know in this regard.....

    Thanking You,

    With Regards,

    Sonjoe Joseph.
  8. vletm

    vletm Excel Ninja

    Messages:
    4,805
    1. modified
    2. missed - modified
    3. from Your original needed sample cvs-file ... which You've asked to do!
    Sample result should be correct!
    Screenshot 2019-01-18 at 19.09.37.png and
    Your 4. Now we need to format the date column dd-mm-yyyy
    ... and now ... hmm? should be dd/mm/yy BUT dd/mm/yyyy is Okay ... Yes?
    >> I took that 'looks time part away'!
    4. No idea of any backfill nor any backfill process!
    Could You do it manually?

    Attached Files:

    • SJ.xlsb
      File size:
      26.9 KB
      Views:
      2
  9. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    Hello Vletm,

    I selected the file and once the process was over the NTY19JANFUT.csv file time format was completely wrong when compared with the NIFTY19JANFUT.csv

    Please do the necessary coding corrections friend and send me an updated file ok.

    Thanking You

    With Regards,

    Sonjoe Joseph
    (Error Screenshot attached)

    Attached Files:

  10. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    I'm sending a file to you & i selected these file and the end result which i got was the time format was completely wrong. Please do check and select this file and see the result.

    Attached Files:

  11. vletm

    vletm Excel Ninja

    Messages:
    4,805
    Did You read my previous reply?
  12. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    Yes i have read ....Friend i think u have selected the wrong file NTY19JANFUT.csv for the process. I have send u the file just now above ie NIFTY19JANFUT.csv Do select this file and see the result. The time formats are wrong rest all is correct. i'm online now.

    Regards,

    Sonjoe Joseph
  13. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    Do u have teamviewer so that i can show u
  14. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    Teamviewer ID:858392603
    Passd: syx972
  15. vletm

    vletm Excel Ninja

    Messages:
    4,805
    I don't know about thinking ...

    but if the 1st time Your CSV-file has this format
    Screenshot 2019-01-19 at 19.09.34.png
    >> compare so call 'time part'
    and next time format as below then MESS!
    Screenshot 2019-01-19 at 19.10.40.png
    Those ORIGINAL CSV-files have to have always SAME FORMAT!
  16. vletm

    vletm Excel Ninja

    Messages:
    4,805
    ... as well as so call 'date part'.
  17. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    Can u come to teamviewer i will show u right now.
  18. vletm

    vletm Excel Ninja

    Messages:
    4,805
    No - I need answers - clear answers to my notes which would help You!
  19. vletm

    vletm Excel Ninja

    Messages:
    4,805
    How many variations there will be with 'Date' in CSV-file?
    So far, two!
  20. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    Dear Vletm,

    Forget everything ur just confusing me a lot. Attached is the file which i just downloaded from my trading system which contains 15 days Intra day data ok. Now with ur excel file which u have made select the file which is attached & just see the result. You will be the dates in the like this as 24:00, 22:00 like that ya.

    Regards,

    Sonjoe Joseph

    Attached Files:

  21. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    Sorry its not the dates its the time format completely wrong ok
  22. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    Time formats are looking like 24:00, 21:00, 20:00 like that which is completely wrong. Dates and rest of all are correct. Only the time format is wrong ok
  23. vletm

    vletm Excel Ninja

    Messages:
    4,805
    I skip 1st line
    I skip 2nd line
    ... it's You who try to get help, not me.
    If You cannot give needed answers, it's challenge!
    I know why those 'time'-outputs are just as those are,
    because Your files haven't been fixed!
    > data has to have fixed format every time! <
    As well as 'dates' ... You haven't answer which that should be ...

    I don't know Your ur, ya or something.

    Attached Files:

    • SJ.xlsb
      File size:
      28 KB
      Views:
      3
  24. vletm

    vletm Excel Ninja

    Messages:
    4,805
    Time formats are looking like 24:00, 21:00, 20:00 like that which is completely wrong.
    Seems that You haven't read my replies...
    Should I also start to be 'c...g' something too?
  25. SONJOE JOSEPH

    SONJOE JOSEPH Member

    Messages:
    239
    In the orginal file which is NIFTY19JANFUT.csv you will see Date as 18/01/2019 15:29 ie Dates and time are together ok. Once the process is done that is when the NIFTY19JANFUT.csv is selected the resulting file which is NTY19JANFUT.csv should show
    Date Time
    18/01/2019 15:29

    Hope now u understood and rest of all u have already done only the Date and time part should look like above. Now Date is fine only the time part is wrong ok
    Now time part is looking like this 24:00 which is wrong.

    Regards,

    Sonjoe Joseph

Share This Page