Here we have a weekly time series: each week, a value.
For each value, we calculate the 1, 4, 13 e 26 week rate of change.
At this point, we need to compare this data with all previous data (historical weekly data goes back to 2004) in order to find and sort all years similar.
So we need to define a standard to quantify the deviation of historical data (4 weekly RoCs) with current one.
I developed a sheet where I try this. Clearly, we need to block last values, 'cause each previous row needs to be compared with always the latter one. A rather time consuming task, should this routine be done on a weekly basis.
I repeat: we need to find the group of data (1w, 4w, 13w and 26w RoC) most similar to the latest one. Before that, we need to gauge how much each previous data was near/far from latest one.
Once measured the deviation in absolute term, we need to relativize it by calculating a percentile value: the difference, in absolute value, need to be referenced to the highest/lowest historical value. Then we can compare apples with apples...
After calculating, for each date, the relative distance with latest data, we could add the four deviation in order to get a score. Like golf, the weeks with lowest handicap (pardon: cumulative distance) are the most similar to latest one.
At this point, we can take the dates, the final score; then we can sort data from lowest to highest, getting the ten most similar row to the latest one.
At this point, we could plot a line chart, showing the average/median value for this cluster of data, 26 weeks before and after the date chosen. Then, we can add the latest 26 weeks of data, wishing that it compares favorably with average/mean value, and hoping that future data will align with the average/mean data.
Obviously, in order to calculate an average, we need to deal with homogeneous data: the values need to be inxeded, by putting equal to "100" the data corresponding to the date selected.
This task can be done manually. It requires some time.
Above all, since there are many absolute reference, the following week is not possible to copy the formulas in the newer row: you need to rewrite them by moving the absolute reference, and than copy and pasting the newer formulas backward.
Moreover, other ten "best" dates will be selected, and another chart will born...
My purpose is to obtain an Excel file that, by just inserting the latest value, will do all the work for me, providing the line chart showing the "best path".
A look at the Excel file I developed will help to better understand my needs.
"""I am an Excel Expert with Professional experience working with Excel both with Formulas and Macro. Working with complicated formula and Developing Macro programs are part and parcel of my daily work. Completed mor Plus
17 freelance font une offre moyenne de €152 pour ce travail
Hello? How are you? I am excited to work with you on this project. This is what i have done in Excel/Word/PPT/Access/VBA/MACRO. Just Creating forms, query, Macro, custom formula, etc... [login to view URL] Plus
Hello! I understood your project. I see that you need some help for programming based on excel file. I am on freelancer all day and I will implement all things that you want perfectly. I can help with that and can star Plus
Hi, Would you like to see a quick demo of your project on developing an Excel program that will automate the data calculation process and provide the line chart that shows the "best path" completed before you award the Plus
-Went through the [login to view URL] file. Foglio1 - I understand there are weekly closing prices and Variance% for 1w, 4w, 13w and 26w are calculated. - Based on the current week close data, the remaining variances and the Plus
Hello.. I have read your post very carefully and I have understood what you want. I am a 100% completion Developer. I have experience of development like this. As an experienced developer, I am sure I can handle your p Plus
Dear, Sir. Call me, KuanHui. I read through the job details extremely carefully and I am absolutely sure that i can complete your project on time and within your budget. I hope to discuss more with you. Thank you so mu Plus
Hi Good Day! A serious freelancer here. I can help you to make it automated using Excel Formula and VBA Macro. Please consider my bid. I've done a lot of Reports and Templates using MS Excel. I can use my expertise Plus
i checked the attached excel file i also read the description i will provide it to you within 5 days
Im a good designer with a team of experts in IT that can help shape the desired web Design. I would love to work with you so that you can realise your dream web design. I hope that together we can make this a reality. Plus
Hello. I bid for this project because I'm a professional with a lot of experience in MS Excel and VBA programming (macros). I guarantee you an accurate job, full functionality and fast delivery, all this in exchange fo Plus
Hello. Welcome! I'm excited that you’re here! *****Excel VBA Macro Scrapping Expert****** 1. I am an expert in Excel(VBA), Excel formulas, and all excel functions, macros, lookup, pivot tables and charts. 2. Have done Plus
Hello, My name is Salim. I just saw your project description carefully. I have lots of experience in data typing, data entry, web research, database administration, ad posting, data research, data mining, and data ext Plus
Welcome, The Global Translations [login to view URL] have an Excellent Group of Professional.5 years experience in Data processing ,VOICE OVER & PROOFREADING,Data entry, training courses from all the important Languages and Data Plus