+ Reply to Thread
Results 1 to 38 of 38

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

  1. #1
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,767

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

    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
    Attached Files Attached Files
    Last edited by Greg M; 11-30-2021 at 01:46 PM. Reason: Typo corrected

  3. #3
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    Quote Originally Posted by Greg M View Post
    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!!!!...

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,767

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

    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
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    Quote Originally Posted by Greg M View Post
    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.

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,767

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

    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
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    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

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,767

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

    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
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    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.
    Attached Files Attached Files
    Last edited by chandannasta; 12-03-2021 at 04:45 AM.

  10. #10
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,767

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

    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

  11. #11
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    Quote Originally Posted by Greg M View Post
    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

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,322

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

    Quote Originally Posted by chandannasta View Post
    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.

  13. #13
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    Quote Originally Posted by jindon View Post
    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.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,322

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

    Didn't read #9 yet.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    Quote Originally Posted by chandannasta View Post
    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.

  16. #16
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    Quote Originally Posted by jindon View Post
    Didn't read #9 yet.
    It worked. check the 2nd part where main help is needed

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,322

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

    Please Login or Register  to view this content.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    Quote Originally Posted by jindon View Post
    Please Login or Register  to view this content.
    @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.
    Last edited by chandannasta; 12-04-2021 at 10:26 AM.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,322

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

    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.

  20. #20
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    Quote Originally Posted by jindon View Post
    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

  21. #21
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

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

  22. #22
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,767

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

    Hi again,

    I am attaching my final version in case you or others might be interested.

    The following CodeModule has been added:

    Please Login or Register  to view this content.

    Run the "Import New Data" process, then select the dates for which you wish to process data, and click on the second button.

    Hope you find this helpful/interesting.

    Regards,

    Greg M


    P. S. See post #24 for workbook
    Last edited by Greg M; 12-05-2021 at 05:44 AM. Reason: P. S. added

  23. #23
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,322

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

    Quote Originally Posted by chandannasta View Post
    @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.
    Attached Files Attached Files

  24. #24
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,767

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

    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
    Attached Files Attached Files

  25. #25
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    @Greg M Marvellous work
    @Jandon why on second tab even for 0 also its showing at 1, If there is no change in the company then it should show as 0 only and not as 1. 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.
    Last edited by chandannasta; 12-05-2021 at 05:46 AM.

  26. #26
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,767

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

    Hi again,

    See the workbook in post #24.

    Regards,

    Greg M

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,322

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

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

  28. #28
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,322

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

    Quote Originally Posted by chandannasta View Post
    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.

  29. #29
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    Quote Originally Posted by jindon View Post
    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

  30. #30
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,322

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

    Try the last code I posted and see...

  31. #31
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    @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

  32. #32
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    19,322

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

    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.

  33. #33
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    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.
    Attached Files Attached Files

  34. #34
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    @Greg waiting for your reply

  35. #35
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    @Greg still waiting for your reply

  36. #36
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    @Greg still waiting for your reply on post #33

  37. #37
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

    @Greg still waiting for your reply on post #33

  38. #38
    Registered User
    Join Date
    07-30-2013
    Location
    India, Mumbai
    MS-Off Ver
    Excel 2007
    Posts
    72

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to extract daily data from Graphs
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-17-2021, 02:44 AM
  2. Getting stock info from a site automatically on a daily basis
    By dalewms3 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-21-2015, 05:41 AM
  3. Macro for printing page as PDF on daily basis
    By aneshdas in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-05-2014, 10:53 AM
  4. Dash update on daily basis with macro
    By punna111 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-27-2012, 02:33 PM
  5. Replies: 8
    Last Post: 10-12-2012, 12:35 PM
  6. " Save as" a workbook automatically on daily basis
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-18-2012, 10:43 AM
  7. [SOLVED] How do I set up report linking data that changes on daily basis?
    By SrMarsia in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-01-2005, 11:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1