Hello friends,
I have tried a lot for the past few days but am still struggling... hence, finally posting it here...
I have a database of approx 225 stocks. Each stock has records in approximately 1600 rows... thus the total database runs into approximately 360,000 rows... Lets cap it at 500,000 rows... since the number of stocks & number of records of each stock may increase... However, since there is a restriction on the size of the file that I can attach, I am attaching an excel file with data of only 4 stocks.....
Now I want to extract records of 300 rows of each stock... the date can be random..... keeping in mind that the earliest date can be the 300th row in the list of dates (column T).
I have put the formula in column "AI" to pick the 300 dates. Thereafter, based on my limited knowledge, I have put the formula for extracting the open > high > low > close > etc..... (columns AB to AP) for the first stock (NIFTY)....
This is what I am trying to achieve:
I want to extract data for all the stocks for the selected 300 dates... presently, I am not even able to copy the formula down..... it took me over 4 hours and only 25% had been processed.... so if I have to keep changing the date, I really don't know when & how I will be able to do my work....
once the data is extracted, I want to copy > paste value special the extracted data in another file (MAIN_DASHBOARD.xlsm) in the same folder (on a particular sheet (Main Data) > in particular cells (column B to column P, starting in row 3 > rows will be dependant on the number of stocks and each stock will have 300 rows).... thereafter the existing formulae in this file will be computed automatically.... once the complete calculation is completed.... thereafter, the sheet named "DASHBOARD" (for which I have already defined the page set-up), in the same file needs to be saved as a separate file in .xlsx format in the same folder and the file should be saved as Dashboard_(date in 300th row in yyyymmdd format).xlsx....
Once this process is completed I want to extract data by changing the date... starting from date in 300th row, then 301st, 302nd and so on till the last date in column T... and for each date, the entire process of copy > paste special value in the specific Main Data sheet of MAIN_DASHBOARD.xlsm and saving the Dashboard sheet should be repeated for each date....
Can someone help me?
Many thanks in advance....
Bookmarks