Need a macro to automatically extract and provide entire data on daily basis

1. Go to https://www1.nseindia.com/products/c...rchieve_eq.htm

2. Once you open the above link, in Select Report option select Bhavcopy (PR.zip) and choose the date for example 22nd Nov and then click on Get Data.

3. The Winzip file would open. Now in that winzip file extract the file that starts with pd only and this you need to do on daily basis on the days in which stock market is open except weekends and government holidays.

4. Once done and the pd file opens. we just need the symbol, security name and the close price from it. Just these 3 columns only. Nothing else.

5. So under symbol & security, below Nifty 50 sec, you shall see Adani port symbol and adani port & sez ltd. You can copy from there till above Other securities. You dont have to copy anything related to other securities or below that.

6. I am sending you the way I do so that you get rough idea. I want to get this automated instead of doing this everyday manually by running a macro. I am sending you so that u can see how I make

Note: I used only copy, paste, vlookup and trim on daily basis since past so many years. Now I feel if I get a macro to get this automated it would save a lot of time. I have attached the examples of what I do on daily basis.

Originally Posted by Greg M

Hi there,

See if the attached workbook does what you need. It uses the following code:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.

Open the data workbook (Pdnnnnnn.csv) from which you want to import the new data. Open the attached workbook. Click on the "Import New Data" button.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Greg M, I must say the work that you did is a lot amazing and its very very nice. You are like a god for me. There is 1 minor change that it needs it doesn't add any new companies. If you check My OVERALL 23rd Nov 2021.xlsx‎ (848.6 KB, 1 views) file the count shows 2034 in total and when I run the import count it shows only till 2031 and when you check OVERALL 24rd Nov 2021.xlsx‎ (861.8 KB, 0 views) Download the count shows 2035 and when I run the import data that you made it shows count 2031 only. So new companies don't get added. If you can make these changes in the sheet as I want the data to be pulled from pd file in the below ss mentioned.

I take only these 3 columns SYMBOL, SECURITY and CLOSE_PRICE and below Nifty 50 sec I copy from there "https://ibb.co/zm8pJJ1" until above Other securities "https://ibb.co/f2Fkmhz". These both are the screenshots for 23rd November 2021 pd file. Check both the screenshots please. So what happens no matter how many new companies come every single day gets added in between that only. So every single day I do the same thing. But your 1 click will save me from my backache and save a lot of time from sitting in front of my computer for hours. I sit like 17-18 hrs a day watching videos related to stock market and try learning chart patterns and make excel sheets and all and invest accordingly. Thank you so much for all your help. Thank you thank you thank you!!!!...

Hi again,

Many thanks for your feedback, your kind words, and also for the Reputation increase - much appreciated!

There is 1 minor change that it needs it doesn't add any new companies

That "minor change" required quite a bit of additional coding, but it was an interesting challenge!

The attached workbook appears to do what you need. The amount of code it contains is such that including the code here would cause this post to exceed the maximum number (15,000) of characters allowed, so I have not included the code in the post.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Originally Posted by Greg M

Hi again,

Many thanks for your feedback, your kind words, and also for the Reputation increase - much appreciated!





That "minor change" required quite a bit of additional coding, but it was an interesting challenge!

The attached workbook appears to do what you need. The amount of code it contains is such that including the code here would cause this post to exceed the maximum number (15,000) of characters allowed, so I have not included the code in the post.


Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Thank you so much. Now the new companies are adding up properly. But for 22nd Nov, 24th Nov, 29th Nov, 1st & 2nd dec I get run time error 9 subscript out of range with 3 options End, Debug and Help when I click on Import New Data. Here is the ss "https://ibb.co/z7HQ07W". Please if you can fix this up.

Hi again,

Apologies - the application failed because no NEW companies were identified on those days.

The code in the attached workbook addresses this problem.

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

Greg so there is a problem in your code. Your 11.xlsm sheet code cant check any new companies on any day at all but your 10.xlsm sheet can check new companies but on the day when there are no new companies it cant check old companies prices on those specific days

Hi again,

The problem was caused by the fact that the Excel "Transpose" function does not appear to work correctly with an array declared as multi-column but having a value of one specified for the number of columns involved.

The attached workbook contains a workaround to overcome this.

Regards,

Greg M

Hi again,

I can't promise anything, but I'll try to take a look at this in the next few days if time permits.

Regards,

Greg M

Originally Posted by Greg M

Hi again,

I can't promise anything, but I'll try to take a look at this in the next few days if time permits.

Regards,

Greg M

Sure will wait for your reply asap. Thanks a lot because the 2nd and the last part only is the main part

Originally Posted by chandannasta

5. So under symbol & security, below Nifty 50 sec, you shall see Adani port symbol and adani port & sez ltd. You can copy from there till above Other securities. You dont have to copy anything related to other securities or below that.

Different method.
Just select the folder that contains csv file.

Please Login or Register  to view this content.

Originally Posted by jindon

Different method.
Just select the folder that contains csv file.

Please Login or Register  to view this content.

@Jindon I am not so good with all this stuff as I am not so good with all the code. If you can just give me an excelsheet with this macro in it so that I just have to click on a single button automatically and it can extract the data. That would be easy for me. Also if you can check my post # 9 in this thread. I really appreciate your help bro. Thank you so much.

Didn't read #9 yet.

Originally Posted by chandannasta

Awesome Greg Love u this sheet really worked really very well. It showed everything that was needed for the 1st part. Now the 2nd and last part I don know if its easier for you or not.

Note: Make sure before doing this this you replace all the # n/a, N/A with all single 0 in entire sheet on new tab when copying and pasting the data.

Now here is the 2nd part of the sheet which I have marked in red I do and taken from the macro by running it on the 1st part known as sp1 sheet till current date and copied and pasted on new sheet called as Frnds:

1. First I copy the Code and Company Name in new tab on excel sheet from the 1st part of the sheet by running the macro you provided so I have everything handy.
2. Lets say for example I take the data of 25th aug to 31st aug. I copy that
3. So what I do is take the 1st co value from 26th aug and minus that with the same company value on 25th aug. So if you see my 1st co was 20 microns and the value on 26th aug was 52.75 and on 25th aug the value was 53.3 so when I minus that I get -0.55 which is mentioned on the right. So like this I do on daily basis. Minus the value of the current day with the previous day with the data in hand. So the 1st part of the sheet is attached named as frnds which I do it on daily basis.

Now another part that I do is check how many days/weeks/months how many companies are going up and down in a day, 2 days, 3 days, 4 days, 5 days and so on and in order to do that I do this which I have marked in yellow in frnds 1 sheet

1. Apply filter on entire excel sheet
2. Then sort the numbers where we just collected the entire data the minus thing from largest to smallest
3. As you can see I only have the data from 26th aug onwards so I will take that and sort from largest to smallest.
4. So all companies whose priced increased with positive numbers will come up for 26th aug and all negative numbers and companies with no changes will go down.
5. So the one with positive numbers which means value increased I marked them as 1 and once in which there is no change at all or the value has decreased I mark them as O. So if you see for 26th aug the higest positive value in red shows as 463.45 so I had sorted for 26th aug only from largest to smallest. So higest change was rs 463.45 for company called P&G HYGIENE & HEALTH CARE and the lowest positive change was 0.05 for a co called UNION BANK OF INDIA and after that all negative and no changes in company started which I marked as 0
6. Same procedure I do for 27th, 30th and 31st aug also


So I want to get the above entire thing automated via macro rather then doing it manually. I invented all the above things on my own by studying and studying and researching and making it very very simple for a person to make money in my own way. In the above way I come to know which company stocks are at upper circuit most of the time.

@Jandon This is the main part where much help is needed.

Originally Posted by jindon

Didn't read #9 yet.

It worked. check the 2nd part where main help is needed

Please Login or Register  to view this content.

1
Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

2
Shutdown my pc already, so tomorrow.

Originally Posted by jindon

1
Please don't quote whole posts -- it's just clutter.*If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

2
Shutdown my pc already, so tomorrow.

alright no worries thank you so much for your help. Really appreciate it bro

I am closing this post as my work is done. Thank you so much. to Greg and Jindon.

Originally Posted by chandannasta

@Jandon. Thank you but can you join both the codes so that when on 1st tab the data is extracted from the folder and as soon as that is done and completed by the macro then on 2nd tab automatically the macro in the same excel sheet should run and complete the 2nd part also.

To Sheet1 code module

Please Login or Register  to view this content.

Hi again,

Apologies! It was late at night and I didn't notice that the system did not attach the file because it was too large

Regards,

Greg M

Hi again,

See the workbook in post #24.

Regards,

Greg M

Then I don't understand the rule for 1/0.

Originally Posted by chandannasta

By the way amazing work. The only problem with ur code is that I have to extract first all the files in 1 folder and then only it can grab that file otherwise it cannot. It should be easier in such a way that as soon as the winzip file is open it can easily grab the file from it and give me the results.

Place all the related csv files in one folder.

Please Login or Register  to view this content.

Originally Posted by jindon

Then I don't understand the rule for 1/0.

@Jandon when I sum up the total say suppose all 20 days its 1 then that means that specific company stock is @ upper circuit. So I will put my money on that stock. I hope you got my point

Try the last code I posted and see...

@admin I have 2 more sheets. 15-20 yrs of hard work of mine was completed juz in 48 hrs by these 2 ppl from your forum Greg M and Jindon and for the next 100 years they have made the work easier for me and entire global world for my 1st sheet. I have 2 more sheets. Should I paste in the same thread in which I am doing it now and continue it or should I open up a fresh new post let me know because 2nd and 3rd sheet is 90 % same and only 10% changes

Greg may understands better than me as I don't know if my last code is working 100 % or not so far, so stick with him.

1st Part of the file for NSE data

1. Go to https://www1.nseindia.com/products/c...rchieve_eq.htm

2. Once you open the above link, in Select Report option select only Bhavcopy. There would be 2 files with that name Bhavcopy and Bhavcopy (PR.zip) but you have to select only Bhavcopy and choose the date for example 22nd Nov and then click on Get Data.

3. The Winzip would open with one single file. Now extract that file and this you need to do on daily basis on the days in which stock market is open except weekends and government holidays.

4. Once done and the file opens. we just need the ISIN, SYMBOL and LAST( which shows all the prices). Just these 3 columns data only. Nothing else. Makes sure you move the ISIN from right to the left and then make sure you remove all the duplicate ISIN with all the complete data of that specific ISIN if available any from entire data.

5. Now you have to copy that entire data on daily basis as per date wise.

6. I am sending you the way I do so that you get rough idea. I want to get this automated instead of doing this everyday manually by running a macro. I am sending you so that you can see how I make. Example Files are attached with NSE OVERALL DATA for dates 18th Nov, 22nd Nov, and 23rd Nov in the file known as ch.

Note: I used only copy, paste, vlookup and trim on daily basis since past so many years. Now I feel if I get a macro to get this automated it would save a lot of time. I have attached the examples of what I do on daily basis.

Once the above 1st Part is done 2nd part of the file for NSE data details below:

Note: Make sure before doing this this you replace all the # n/a, N/A with all single 0 in entire sheet on new tab when copying and pasting the data.

Now here is the 2nd part of the sheet which I have marked in red I do and would take from a macro that you shall make by running it on the 1st part known as sp1 sheet till current date and copied and pasted on 2nd tab from the 1st tab:

1. First I copy the ISIN and Symbol in 2nd tab on excel sheet from the 1st tab of the sheet by running the macro that you would provide so that I have everything handy.
2. Lets say for example I take the data of 25th June to 30th June. I copy that
3. So what I do is take the 1st Symbol value from 28th June and minus that with the same symbol company value on 25th June. So if you see my 1st symbol was 1018GS2026 and the value on 28th June was 0 and on 25th June the value was 0 so when I minus that I get 0 which is mentioned on the right. So like this I do on daily basis. Minus the value of the current day with the previous day with the data in hand. So the 1st part of the sheet is attached named as SP1 which I do it on daily basis and then the 2nd part is also run on daily basis which is shown in red in Frnds sheet.

Now another part that I do is check how many days/weeks/months how many companies are going up and down in a day, 2 days, 3 days, 4 days, 5 days and so on and in order to do that I do this which I have marked in yellow in frnds 1 sheet

1. Apply filter on entire excel sheet
2. Then sort the numbers where we just collected the entire data the minus thing from largest to smallest
3. As you can see I only have the data from 28th june onwards in red so I will take that and sort from largest to smallest.
4. So all symbols of companies whose priced increased with positive numbers will come up for 26th June and all negative numbers and symbols of companies with no changes will go down.
5. So the one with positive numbers which means value increased I marked them as 1 and once in which there is no change at all or the value has decreased I mark them as O. So if you see for 28th June the higest positive value in red shows as NIFTYEES so I had sorted for 28th June only from largest to smallest. So higest change was Rs 20018 for symbol company called NIFTYEES and the lowest positive change was 0.01 for a symbol company called ICICIBANKP and after that all negative and no changes in symbol company started which I marked as 0
6. Same procedure I do for 29th and 30th June and so on for all future dates

So I want to get the above entire thing automated via macro rather then doing it manually. I invented all the above things on my own by studying and studying and researching and making it very very simple for a person to make money in my own way. In the above way I come to know which company stocks are at upper circuit most of the time.

@Greg waiting for your reply

@Greg still waiting for your reply

@Greg still waiting for your reply on post #33

@Greg still waiting for your reply on post #33

@Greg M still waiting for your reply on post #33

@Greg M still waiting for your reply on post #33

Can anyone help for Post # 33 please?

Can anyone help for Post # 33 please?

Can anyone help for Post # 33 please?

Can anyone help for Post # 33 please?

Can anyone help for Post # 33 please?

Originally Posted by AliGW

I think it's pretty clear that your last request has put your helpers off. It's rather more like a brief than a request for help, and so I'd suggest you purchase points with which to pay someone and post in the commercial services section. You aren't asking for help - you are asking someone to build a whole solution for you.

I tried to purchase and this is what I received https://ibb.co/R7BnDjS