• 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.

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

SONJOE JOSEPH

SONJOE JOSEPH

Member
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
 

Attachments

  • Downloaded file from Trading Terminal.png
    Downloaded file from Trading Terminal.png
    239.7 KB · Views: 18
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.
 
SONJOE JOSEPH said:
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
Click to expand...



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

https://www.powerquery.training/book-files/
 
vletm said:
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.
Click to expand...

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
 

Attachments

  • NIFTY19JANFUT.csv
    241.7 KB · Views: 10
  • NTY19JANFUT.csv
    294.4 KB · Views: 5
  • NTY19JANFUT.csv
    333.9 KB · Views: 4
  • CSV files location.png
    CSV files location.png
    191.9 KB · Views: 5
  • Amibroker Database Path.png
    Amibroker Database Path.png
    220.5 KB · Views: 6
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?
 

Attachments

  • SJ.xlsb
    24.2 KB · Views: 8
vletm said:
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?
Click to expand...

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.
 
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?
 

Attachments

  • SJ.xlsb
    26.9 KB · Views: 3
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)
 

Attachments

  • Time format wrong.png
    Time format wrong.png
    274.3 KB · Views: 8
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.
 

Attachments

  • NIFTY19JANFUT.csv
    254.7 KB · Views: 2
vletm said:
Did You read my previous reply?
Click to expand...

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

Attachments

  • NIFTY19JANFUT.csv
    254.7 KB · Views: 0
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
 
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.
 

Attachments

  • SJ.xlsb
    28 KB · Views: 5
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?
 
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
 
You must log in or register to reply here.
Back
Top