Data presentation in a particular format using two other excel files?

[SONJOE JOSEPH]

Dear Excel Experts,

Every day i used to download the indices and stock details file from the nse website. The first file named ind_close_all_21042023 contains all the indices data and the sec_bhavdata_full_21042023 contains all the stocks data. The NSE updates this files on a end today basis and the file name is changed frequently according to date. By using this two files i just want the datas according to the order <ticker>,<date>,<open>,<high>,<low>,<close>,<vol>,<o/i>

My idea is to have a new excel sheet. Add two command button. First Command button name "Insert files" which will insert the selected files i selected. The second command button name "Processing" which will combine both the selected files and the data should be in the order <ticker>,<date>,<open>,<high>,<low>,<close>,<vol>,<o/i> The final processed file should be a notepad file were the file name will be in the format YYYY-MM-DD-NSE-EQ. This processed file should contain the index data first and then the stock data.

Please help me in this regard, I'm attaching the bhavcopy files and the final result file which is notepad file just to get and idea how the data in presented in the notepad file ok.

Thanks & Regards,

Sonjo

 

[SONJOE JOSEPH]

Note:-

In the sec_bhavdata_full_21042023 care should be taken only to take datas which contains only EQ and BE in column named "SERIES" rest of the datas should be avoided ok

 

[Marc L]

Hello, if the purpose is to just convert two .csv files to a single .txt file it can be achieved directly under Windows without Excel …​

Anyway, as we are on an Excel VBA forum section, a VBA procedure can do it without loading anything in a worksheet​

just with exactly the same way you already got in a previous thread ‼​

For more help, according to the .txt result file 'columns' order, share at least their relavive 'columns' for each source csv file​

and add any necessary information - how that should work - in order there is nothing to guess​

or you will have to fit yourself the code for what your forgot / misexplained …​

​

 

[SONJOE JOSEPH]

Marc i didn't understand what do u mean by a relative column. the idea here is combine data just like the text file given in the notepad. Even without vba u can do it do help me out. Once the data is like the notepad text file then i can upload into my charting software.

 

[Marc L]

The idea is you must indicate which columns / fields - whatever - for both source notepad text files​

must be exported to the notepad result text file according to its expected order and​

do not forget to share a better explanation how the VBA procedure should work​

as guessing can't be helping neither coding …​

​

 

[SONJOE JOSEPH]

Marc sorry for the delay in reply since i was not welll. I'm giving my explanation in a step by step process given below:-

1. First open a blank excel file. Adding two command buttons. First command button name "INSERT FILE" & Second command button name "PROCESSING".
2. Once the user click the "INSERT FILE" the Open dialogue box has to open were the user can search the files from the download folder in his pc. Then insert the files sec_bhavdata_full_210423 as well as the ind_close_all_210423. Here i'm inserting only the date 210423 files. Suppose if the user has forgot to update any missed dates then he can select those files too.
3. Once the user has selected the files, he will click the "PROCESSING" button. Here it has to do the following things given below:-
a) From the sec_bhavdata_full_210423 in the series column i want those data which have "BE & EQ" rest of the data's are of no use. Normally here when i do manually i just choose Data -> Filter - Uncheck All and then click only BE and EQ. This is how is used to filter my data
b) From the ind_close_all_210423 there is nothing to filter
4. Finally once the processing is complete the system has to open automatically a notepad file with the name yyyy-mm-dd-NSE-EQ which contains data of both sec_bhavdata_full_210423 as well as ind_close_all_210423.

The notepad file data has to be according to the following format <ticker>,<date>,<open>,<high>,<low>,<close>,<vol>,<o/i>

If you open the sec_bhavdata_full_210423 your will see "SYMBOL" which is <ticker>, "Date1" is <Date>, OPEN_PRICE is <open>, HIGH_PRICE is <high>, LOW_PRICE is <low>, CLOSE_PRICE is <close>, TTL_TRD_QNTY is <vol>, DELIV_QTY is <o/i>

If you open the ind_close_all_210423 you will see "Index Name" which is <ticker>, Index Date is <Date>, Open Index is <open>, High Index is <High>, Low Index is <low>, Closing Index is <close>, Volume is <vol> and there is no <o/i> for index for that the value will be 0 for all index.

Once this is all done the notepad file should first show the indexdata then followed by the script data. This is the way the data is order in the notepad file named 2023-04-21-NSE-EQ

Hope i have explained it very well. Awaiting for your solution

 

[Marc L]

As the blanks are now filled that's doable just with a single button but according to​

Once this is all done the notepad file should first show the indexdata then followed by the script data.

you can't have​

Suppose if the user has forgot to update any missed dates then he can select those files too.

So only a day will be processed according to the unique file chosen by the user,​

the VBA procedure will search the other relative text file according to the date if only located in the same folder​

(ready solution according to a previous thread about a similar subject you already asked for)​

in order to create the expected result text file in the same folder …​

​

Or the user must select all the source text files to process - whatever for a single day or several days - but​

the VBA procedure will not check if the user has forgotten any file !​

​

If under Windows only (?) what's your process choice :​

1. an unique day according to the single csv text file chosen by the user (so the EZ way)
   
   
2. unique or several days whatever but according only to the csv text files chosen (should work only with smart enough users)
   
   
3. the VBA procedure converts all the csv text files of 'Downloads' folder ?
   (Once done the VBA procedure should move the csv files to the specific folder where the result text files are …)