Terminé

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.

Compétences : Excel, Visual Basic, Traitement de Données, Saisie de Données, Architecture Logicielle

en voir plus : 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

Concernant l'employeur :
( 1 commentaire )

Nº du projet : #21390701

Décerné à:

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 Plus

%selectedBids___i_sum_sub_7% %project_currencyDetails_sign_sub_8% EUR en 7 jours
(230 Commentaires)
6.9

17 freelance font une offre moyenne de €152 pour ce travail

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

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 3 jours
(154 Commentaires)
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 Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 3 jours
(21 Commentaires)
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 Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 7 jours
(50 Commentaires)
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 Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 7 jours
(53 Commentaires)
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 Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 2 jours
(14 Commentaires)
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 Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 4 jours
(9 Commentaires)
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 Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 3 jours
(32 Commentaires)
4.4
Lionanaimal

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

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 7 jours
(12 Commentaires)
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. Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 2 jours
(8 Commentaires)
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 Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 3 jours
(15 Commentaires)
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 Plus

%bids___i_sum_sub_32% %project_currencyDetails_sign_sub_33% EUR en 1 jour
(0 Commentaires)
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 Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 2 jours
(0 Commentaires)
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 Plus

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 2 jours
(0 Commentaires)
0.0
%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 3 jours
(0 Commentaires)
0.0
%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 3 jours
(0 Commentaires)
0.0
Specialmuanya

Will deliver on time and all accuracy

%bids___i_sum_sub_35% %project_currencyDetails_sign_sub_36% EUR en 5 jours
(0 Commentaires)
0.0