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

vletm · Jan 23, 2019

SONJOE JOSEPH
yes ..
if person, SONJOE JOSEPH, has seen few times text as ReRead #45.
And now, he haven't understand --- what? ... yes!
I'll try one more time:
ReRead #45 is refer to #45 Reply,
which is You should learn to read, answer to questions ... Okay?

Now, careful ... small steps ...
I try to ask questions and You try to answer Yes/No?

1) Your the latest sent file: NIFTY19JANFUT.csv is as original file as possible? Yes/No?

2) Your the latest sent file: NTY19JANFUT.csv is file which You have done MANUALLY? Yes/No?

3) Have You tested, do that file work with 'Amibroker'? Yes/No?

4) You've written that there are some wrong dates?
below is parts of from YOUR the latest sent NTY19JANFUT.csv - Yes/No?

... anyway ...

As You see,
1/2/2019 ... maybe 2nd of January
1/1/2019 ... maybe 1st of January
31/12/2018 ... maybe 31st of December
>> something no match! <<
the 1st number is Month in 2019 and the 1st number is Day in 2018
the 2nd number is Day in 2019 and the 2nd number is Month in 2019
... years are always 3rd numbers - good!
((( with NIFTY19JANFUT.csv those dates are all as dd/mm/yyyy )))

5) If that file (NTY19JANFUT.csv) is NOT made MANUALLY,
then do it AGAIN MANUALLY
from Your the latest sent NIFTY19JANFUT.csv -file. Okay?
>> test that file with 'Amibroker' - okay?
>> upload it here - okay?

SONJOE JOSEPH · Jan 23, 2019

vletm said:
SONJOE JOSEPH
yes ..
if person, SONJOE JOSEPH, has seen few times text as ReRead #45.
And now, he haven't understand --- what? ... yes!
I'll try one more time:
ReRead #45 is refer to #45 Reply,
which is You should learn to read, answer to questions ... Okay?

Now, careful ... small steps ...
I try to ask questions and You try to answer Yes/No?

1) Your the latest sent file: NIFTY19JANFUT.csv is as original file as possible? Yes/No?

2) Your the latest sent file: NTY19JANFUT.csv is file which You have done MANUALLY? Yes/No?

3) Have You tested, do that file work with 'Amibroker'? Yes/No?

4) You've written that there are some wrong dates?
below is parts of from YOUR the latest sent NTY19JANFUT.csv - Yes/No?

... anyway ...
View attachment 57750
As You see,
1/2/2019 ... maybe 2nd of January
1/1/2019 ... maybe 1st of January
31/12/2018 ... maybe 31st of December
>> something no match! <<
the 1st number is Month in 2019 and the 1st number is Day in 2018
the 2nd number is Day in 2019 and the 2nd number is Month in 2019
... years are always 3rd numbers - good!
((( with NIFTY19JANFUT.csv those dates are all as dd/mm/yyyy )))
5) If that file (NTY19JANFUT.csv) is NOT made MANUALLY,
then do it AGAIN MANUALLY
from Your the latest sent NIFTY19JANFUT.csv -file. Okay?
>> test that file with 'Amibroker' - okay?
>> upload it here - okay?

Dear Friend,

1. No. I have taken some parts of the dates which are getting wrong in the "NTY19JANFUT.csv" file. I attached this NIFTY19JANFUT.csv just to compare the dates with the "NTY19JANFUT.csv". Since both the NIFTY19JANFUT.csv & "NTY19JANFUT.csv" files is more than 1 mb i can't attach here.

2. This latest "NTY19JANFUT.csv" file is not a manual work. This file was automatically created once i run ur latest file in #68 which u provided ok

3. No. I have not tested in Amibroker since already the dates are wrong there is no point in testing it in Amibroker charting software.

4. Date format has to be in these way dd/mm/yyyy hh:mm so the correct way is 02/01/2019 hh:mm, 01/01/2019 hh:mm (1st January,2019), the other one is already correct. Please compare the master file "NIFTY19JANFUT.csv" were the dates are in the correct format so thrdr dates has to be the same in the "NTY19JANFUT".csv ok

SONJOE JOSEPH · Jan 23, 2019

5. This i should do it manually or should i do it from ur latest SJ file and the backfill result u want the result to upload it here.. Tell me clearly which one u want ok

vletm · Jan 23, 2019

I tried to get only YES/NO -answers ..
1) ... okay NO
2) ... seems to be NO
3) ... okay NO
4) I compared files, BUT
I need Your MANUALLY MADE FILE to see what is there!
And that file has to be tested with 'Amibroker' too!

5) If I wrote:
If that file (NTY19JANFUT.csv) is NOT made MANUALLY, then do it AGAIN MANUALLY
Then
> do it MANUALLY as few months ago!
> test is with 'Amibroker'
> Send BOTH (original and after manually made) files here
(( or at least those possible challenge parts ))

SONJOE JOSEPH · Jan 23, 2019

O

vletm said:
I tried to get only YES/NO -answers ..
1) ... okay NO
2) ... seems to be NO
3) ... okay NO
4) I compared files, BUT
I need Your MANUALLY MADE FILE to see what is there!
And that file has to be tested with 'Amibroker' too!

5) If I wrote:
If that file (NTY19JANFUT.csv) is NOT made MANUALLY, then do it AGAIN MANUALLY
Then
> do it MANUALLY as few months ago!
> test is with 'Amibroker'
> Send BOTH (original and after manually made) files here
(( or at least those possible challenge parts ))

Attached is the manually made file "NTY19JANFUT.csv" and the screenshot of the Amibroker chart of the backfilled data through the "NTY19JANFUT.csv" Well perfect when done manually.

SONJOE JOSEPH · Jan 23, 2019

Attached is now a "NTY19JANFUT.csv" made by your SJ file. and just see the screenshot of the Amibroker chart. Just see the attached file and once u open u will see all the dates as backwards while the charts are correct. I hope now u understood it well k

vletm · Jan 23, 2019

5) Send BOTH (original and after manually made) files here
You sent ONLY 'manually made'-file! hmm?

6) Is that original file (NIFTY19JANFUT.csv) same as You have sent few times?

> I didn't ask to send something eg after SJ.xlsb!

Bikram Jeet · Jan 23, 2019

Hello Sonjoe,
Here is the solution to your files.

Just open the ".CSV" file in ".xlsm" template file.

Use the following code and paste in the Macro.
Hit Run.

Code:

---
Sub Sonjoe1()
'
' File Update as per custome settings.
'

'
    Sheets("NIFTY19JANFUT").Select
    Sheets("NIFTY19JANFUT").Copy After:=Sheets(1)
    Sheets("NIFTY19JANFUT").Select
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Trading Symbol"
    Range("A2").Select
    Columns("A:A").EntireColumn.AutoFit
    ActiveCell.FormulaR1C1 = "NTY19JANFUT"
    Range("A2").Select
    Selection.Copy
    Range("B2").Select
    Selection.End(xlDown).Select
    Range("A3751").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("A3:A3751").Select
    Range("A3751").Activate
    ActiveSheet.Paste
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A3752").Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Open"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "High"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Low"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Close/Price"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Volume"
    Range("C9").Select
End Sub
---

If you find any problem, let me know!

vletm · Jan 23, 2019

SONJOE JOSEPH
Interesting language settings challenge!
for testing...

vletm · Jan 23, 2019

SONJOE JOSEPH
This have to work ...
I've learnt something new too

The challenge has been
that we have different language settings ...
If run that original.csv -file with Excel,
then eg date (dd/mm mm/dd) as well as decimal separator could change.
> This version will do this procedure almost without 'Excel' <
... .csv -file is result of that SJ.xlsb

Code:

    FN = Dir(FilePath)
    FN = Left(FN, 1) & Mid(FN, 4, 10)
    Open FN & ".csv" For Output As #2
        Open FilePath For Input As #1
            c = 1
            Do Until EOF(1)
                Line Input #1, linefromfile
                If c > 1 Then
                    msg = FN & "," & linefromfile
                Else
                    msg = "Trading Symbol,Date,Open,High,Low,Close/Price,Volume"
                End If
                Print #2, msg
                c = c + 1
            Loop
        Close #1
    Close #2

SONJOE JOSEPH · Jan 23, 2019

vletm said:
SONJOE JOSEPH
This have to work ...
I've learnt something new too

The challenge has been
that we have different language settings ...
If run that original.csv -file with Excel,
then eg date (dd/mm mm/dd) as well as decimal separator could change.
> This version will do this procedure almost without 'Excel' <
... .csv -file is result of that SJ.xlsb

I'm just checking with that file its working perfect. I'm just downloading from my trading terminal the full data...let me c if it words flawlessly ok....Will ping u withen 20 mins.

Regards,

Sonjoe

SONJOE JOSEPH · Jan 23, 2019

Well Vletm ur are super this was well on target. Backfilling was just all fine. Thanks a lots my dear friend Vletm. All this 4 days i was damn disturbing u and atlast it worked happy man.

Thanks a lot ....will not disturb u for quite sometime unless i get some fresh trading ideas. At last one last question how i can a person like me learn this VBA coding in excel do u have some suggestions for me.

Regards,

Sonjoe

THIS TREAD IS FINAL AND OVER AND ITS SOLVED BY VLETM

SONJOE JOSEPH · Jan 23, 2019

Bikram Jeet said:

Hello Sonjoe,
Here is the solution to your files.

Just open the ".CSV" file in ".xlsm" template file.

Use the following code and paste in the Macro.
Hit Run.

Code:

---
Sub Sonjoe1()
'
' File Update as per custome settings.
'

'
    Sheets("NIFTY19JANFUT").Select
    Sheets("NIFTY19JANFUT").Copy After:=Sheets(1)
    Sheets("NIFTY19JANFUT").Select
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Trading Symbol"
    Range("A2").Select
    Columns("A:A").EntireColumn.AutoFit
    ActiveCell.FormulaR1C1 = "NTY19JANFUT"
    Range("A2").Select
    Selection.Copy
    Range("B2").Select
    Selection.End(xlDown).Select
    Range("A3751").Select
    Range(Selection, Selection.End(xlUp)).Select
    Range("A3:A3751").Select
    Range("A3751").Activate
    ActiveSheet.Paste
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A3752").Select
    Selection.End(xlUp).Select
    Selection.End(xlUp).Select
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Open"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "High"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Low"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Close/Price"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Volume"
    Range("C9").Select
End Sub
---

If you find any problem, let me know!

Thank You for responding my friend. I didn't test ur code since my friend Vletm has done the job and he was damn on target for the last 4 days. At last successful.

Even thanking you since u responded to my thread.

Regards,

Sonjoe Joseph

vletm · Jan 23, 2019

SONJOE JOSEPH
Answer to Your question is
... ReRead #45 ... okay?

AlanSidman · Jan 24, 2019

I looked at your closed thread. I used Power Query to obtain the same results that you show in that thread. Here is your file back with the Power Query Mcode.

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"NIFTY19JANFUT O", type number}, {"NIFTY19JANFUT H", type number}, {"NIFTY19JANFUT L", type number}, {"NIFTY19JANFUT C", type number}, {"NIFTY19JANFUT V", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Trading Symbol", each "NTY19JANFUT"),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"NIFTY19JANFUT O", "OPEN"}, {"Date", "TIME"}, {"NIFTY19JANFUT C", "CLOSE/Price"}, {"NIFTY19JANFUT H", "HIGH"}, {"NIFTY19JANFUT L", "LOW"}, {"NIFTY19JANFUT V", "Volume"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Trading Symbol", "TIME", "OPEN", "HIGH", "LOW", "CLOSE/Price", "Volume"})
in
    #"Reordered Columns"

PQ loads the finished information back into the original file. You can copy and paste to a new file if you wish.

SONJOE JOSEPH · Apr 8, 2019

vletm said:
SONJOE JOSEPH
This have to work ...
I've learnt something new too

The challenge has been
that we have different language settings ...
If run that original.csv -file with Excel,
then eg date (dd/mm mm/dd) as well as decimal separator could change.
> This version will do this procedure almost without 'Excel' <
... .csv -file is result of that SJ.xlsb

Code:

FN = Dir(FilePath) FN = Left(FN, 1) & Mid(FN, 4, 10) Open FN & ".csv" For Output As #2 Open FilePath For Input As #1 c = 1 Do Until EOF(1) Line Input #1, linefromfile If c > 1 Then msg = FN & "," & linefromfile Else msg = "Trading Symbol,Date,Open,High,Low,Close/Price,Volume" End If Print #2, msg c = c + 1 Loop Close #1 Close #2

Dear Vletm,

In the SJ file in the "Trading Symbol" column all down i want it as "NIFTY_F1" since my trading terminal has changed. Please make the necessary corrections and send the updated file at the earliest.

Regards,

Sonjoe

vletm · Apr 8, 2019

SONJOE JOSEPH
Hmm...?
First of all,
in the SJ-file ... there is no any columns in use...
What/where something all down ... or something - somewhere has changed?
A challenge to make anything, without needed information.

SONJOE JOSEPH · Apr 8, 2019

Oh Sorry Veitm,

I'm sending the attached file. When this file is selected in the SJ file which u have send me i want the Trading Symbols to be converted into "NIFTY_F1" The SJ file is at message 85 ok

Regards,

Sonjoe

vletm · Apr 8, 2019

Oh Sorry someone ...
If something would convert then there should be something which would convert - okay?
Wasn't that file already converted or something?
Where would like to seen something like 'NIFTY_F1'?

SONJOE JOSEPH · Apr 8, 2019

Dear Vletm,

When u open the NIFTY19APRFUT.csv file you will find under the "Trading Symbol" down u will find all "NIFTY19APRFUT" which i would like to replace all to "NIFTY_F1 using your file which is attached.

Hope now u understood

Sonjoe

vletm · Apr 8, 2019

U
Wasn't that file already converted or something?
SJ.xlsb needs THE ORIGINAL csv-file!
... or do U really would like to run TWO different 'SJ's to get needed result?

SONJOE JOSEPH · Apr 8, 2019

Dear Vletm,

Thank You for correcting me. Due to change in the trading terminal i have a slight change in the CSV file which is attached below. In this there are 7 headers like Trading Symbol, TIme, Open, High, Low, Close/Price, Volume. The corrections to be made are as follows:-

1. In the "Trading Symbol" column down all the "NIFTY19APRFUT" has to be replaced with "NIFTY_F1".
2. The header "Time" has to be changed to "Date".

Please make the above new necessary corrections and send the updated file.

Thanking You,

Regards,

Sonjoe

vletm · Apr 8, 2019

SONJOE JOSEPH
It seems to be mission impossible to get original csv-file!
Without it, I cannot check how this works!
Seem that those 'changes' are not necessary!
Check file and use it as You would like to get the result which maybe look as You or someone would need...
ps Sentence, which the last mark is ? ... is a questions. ... Question means that You should answer to it ... actually, You need that answer!

SONJOE JOSEPH · Apr 9, 2019

Dear Vletm,

Well this is completely wrong the error file is attached as screenshot. The file that i have send u is the orginal csv file. Again attaching the orginal csv file whiich is generated from my trading terminal and the first SJ file which u have send me. This file i have renamed to Backfill01 ok. The following corrections has to be made listed below:-

1. The NIFTY19APRFUT.csv file has to be rename to NIFTY_F1
2. The "Trading Symbol" header down all instead of instead of "NIFTY19APRFUT" has to be replaced with NIFTY_F1
3 The "Time" heading has to be renamed with "Date".

Hope now u understood and please update the necessary corrections.

Regards,

Sonjoe

vletm · Apr 9, 2019

SONJOE JOSEPH
As You don't know what 'question' means ... seems that You don't know what 'original' neither mean.
Your sent 'original' ... something as below

The Original CSV-file look like above which has used with my SJ.xlsb
... or any other xlsb-files which I've made!
Can You see any difference?
Term 'backfill' ... yes ... I still remember that too ... especially, that You didn't want that!

I can also copy & paste below:
Hope now u understood and please update the necessary corrections.
... actually, I don't hope ...

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

Excel Ninja

Member

Member

Excel Ninja

Member

Attachments

Member

Attachments

Excel Ninja

New Member

Excel Ninja

Attachments

Excel Ninja

Attachments

Member

Member

Member

Excel Ninja

Well-Known Member

Attachments

Member

Excel Ninja

Member

Attachments

Excel Ninja

Member

Attachments

Excel Ninja

Member

Attachments

Excel Ninja

Attachments

Member

Attachments

Excel Ninja