Completed

Excel file (program)

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.

Skills: Excel, Visual Basic, Data Processing, Data Entry, Software Architecture

See more: program sort excel file, excel file stand alone program, making excel file stand alone program, create excel file program, spring program read excel file, create stand alone program excel file, add excel file generate reports program, excel file program text file, open microsoft excel file program, convert excel file standalone program, open excel file program, want open excel file program, convert excel file stand software program, opening excel file program, program extract data excel file, turn excel file program, excel file program, read excel file program

About the Employer:
( 1 review )

Project ID: #21390701

Awarded to:

Tanmoy236

"""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 More

€200 EUR in 7 days
(230 Reviews)
6.9

17 freelancers are bidding on average €152 for this job

polarjin2017

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] More

€155 EUR in 3 days
(154 Reviews)
7.1
truepai

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 More

€233 EUR in 3 days
(21 Reviews)
6.9
dvcontact

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 More

€150 EUR in 7 days
(50 Reviews)
6.4
satishtl

-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 More

€250 EUR in 7 days
(53 Reviews)
6.0
phantom1222

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 More

€140 EUR in 2 days
(14 Reviews)
4.8
fivestar10

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 More

€140 EUR in 4 days
(9 Reviews)
4.5
paulodayto08

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 More

€150 EUR in 3 days
(32 Reviews)
4.4
Lionanaimal

i checked the attached excel file i also read the description i will provide it to you within 5 days

€250 EUR in 7 days
(12 Reviews)
4.6
jameskaikai

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. More

€140 EUR in 2 days
(8 Reviews)
3.2
ReneRondon

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 More

€110 EUR in 3 days
(15 Reviews)
3.1
AntonKozel

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 More

€100 EUR in 1 day
(0 Reviews)
0.0
Salimraj21

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 More

€30 EUR in 2 days
(0 Reviews)
0.0
globallanguage89

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 More

€100 EUR in 2 days
(0 Reviews)
0.0
€155 EUR in 3 days
(0 Reviews)
0.0
€155 EUR in 3 days
(0 Reviews)
0.0
Specialmuanya

Will deliver on time and all accuracy

€133 EUR in 5 days
(0 Reviews)
0.0