<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--> Reaching out to seek help from experts to correct one of the SIP date optimizer data analytics that I was creating to manage my MF investments. I will attempt to be concise as far as possible but do let me know if any clarification is required. Thanks for your patience and help in advance.
Here is some context:
Hope this sets the context and I did not miss out any critical information
.
I have been able to make a significant progress to do this data analysis after few hours of struggle.
Now here is my problem for which I am seeking help:
The data analysis seems to be working correctly for most of the SIP dates except in the last couple of dates at the end of the month (problem days highlighted in the excel sheets shared in the below link). If you notice, the number of SIP’s debited are lesser than 56 SIPs in those days, hence the inaccuracy in the no. of units allotted & comparison.
My root cause analysis of the problem suggests problem in the way next working date is computed by my formula. It doesn’t seem to work well in the last 4-5 of the month specially when the next working day happens to fall in the subsequent month.
Any help would be appreciated either through modification of the existing complicated formula or through a new uncomplicated out of the box solution.
Thanks for all the help and thanks once again for reading the entire post patiently. Cheers
Excel file link: https://www.sendfilessecurely.com/getfile.aspx?id=vB4mwXBha4agEWdr9Lcf2PTV6gypyQxrOYg3
Here is some context:
- Based on the SIP date selected (e.g. 1, 7,14,21,28 etc.), the Mutual Fund company auto debits the bank account for the SIP investment on that particular date EVERY MONTH.
- In case, such date happens to be a holiday, it is debited on the NEXT WORKING DAY.
- No. of investment units allotted depends on the NAV on that particular date.
- NAV of the investment fluctuates every day the market is open.
- My attempt is to analyze historic NAV information and identify the SIP date (if chosen) would have resulted into allotment of maximum no. of units.
Hope this sets the context and I did not miss out any critical information
.I have been able to make a significant progress to do this data analysis after few hours of struggle.
Now here is my problem for which I am seeking help:
The data analysis seems to be working correctly for most of the SIP dates except in the last couple of dates at the end of the month (problem days highlighted in the excel sheets shared in the below link). If you notice, the number of SIP’s debited are lesser than 56 SIPs in those days, hence the inaccuracy in the no. of units allotted & comparison.
My root cause analysis of the problem suggests problem in the way next working date is computed by my formula. It doesn’t seem to work well in the last 4-5 of the month specially when the next working day happens to fall in the subsequent month.
Any help would be appreciated either through modification of the existing complicated formula or through a new uncomplicated out of the box solution.
Thanks for all the help and thanks once again for reading the entire post patiently. Cheers
Excel file link: https://www.sendfilessecurely.com/getfile.aspx?id=vB4mwXBha4agEWdr9Lcf2PTV6gypyQxrOYg3
