Need a macro for this to make work easier

[chandannasta]

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 from sp1 sheet.

Now here is the 1st part of the sheet which I have marked in red I do from 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 sp1 sheet
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 for which I need a macro.

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. Thank you for all your help in advance.

[p45cal]

If you're happy for the date columns to be in the same order (not reversed as you have them), it makes the formulae a LOT easier, then you should start with only columns A & B containing codes and co. names, along with as many columns as contain prices, and NOTHING else on the sheet then this macro will add the price differences and 1s and zeroes in new columns to the right. Check the comments in the code for whether you want the formulae to remain or not.
The macro blah works on whichever sheet is the active sheet, so be careful that it's the right sheet. Button at cell D3 on sheet runs that macro.
I'll leave the sorting of the data to you, there's no need to have it sorted to calcuate the values in the new columns.
I dont see a 'sp1' sheet so haven't done any copying of data.

[p45cal]

cross posted without links:
https://chandoo.org/forum/threads/ne...quickly.47263/
https://www.excelforum.com/excel-pro...ml#post5602176

chandannasta, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184

[chandannasta]

If you're happy for the date columns to be in the same order <snip>

Lovely it worked. Thank you so much

[chandannasta]

cross posted without links:
https://chandoo.org/forum/threads/ne...quickly.47263/
https://www.excelforum.com/excel-pro...ml#post5602176

chandannasta, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184

Sorry I didnt knew about this but thank you 17-20 yrs of hardwork your forum and excel forum both have completed in just 24 hrs. Thank you it means a lot to me. Thank you thank you thank you

[chandannasta]

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.