compare data

[Shaun]

Hi all


I am trying to work how to go about getting Excel to select a time period (10/05/2012 to 10/06/2012 for example) then look at the percentage movements in volume for that period for the stock selected and compare it to all periods available (from 01/01/2001 to 28/09/2012 and + or - 5% variance) for all stocks available and then return the close price for the same period beginning the following day.


In the example file provided, the stocks are in different worksheets. In reality, the stocks are in separate workbooks with file names the same as the worksheet names in the example.


Any assistance that could be provided would be greatly appreciated.


https://rapidshare.com/files/97806159/Example.xlsx


Cheers


Shaun

 

[NARAYANK991]

Hi Shaun ,


I have not been able to understand your problem description ; is it possible you can work out one particular example for dates which are present in your worksheet ?


I do not know whether it is a typo , but your data cannot have the dates 10/05/2012 and 10/06/2012 ; I assume it is for the year 2011.


If we take it the dates are in 2011 , what would the output be for any one stock ?


Narayan

 

[juanito]

Hi Shaun - suggest you give an example of exactly what you want to do... I'm lost like Narayan.


@Narayan: Shaun's dates are DD/YY


- Juanito

 

[Shaun]

Hi NARAYANK991 and juanito


Dates...Sorry I have just realised that the dates I used as an example are not available in the workbook.


New Workbook


https://rapidshare.com/files/1378293639/Example1.xlsx


What I would like to be able to do is this:


Select a date range, whether I have a couple of cells one for the start date and one for the end date or something similar. For the example I have picked in blue a date range in worksheet aac.ax. Then Excel looks at the corresponding % volume movements highlighted in green. Excel will then look for a similar % volume "pattern" in the worksheets aac.ax to abc.ax, highlighted in purple on worksheet abc.ax.


From there Excel will then pickup the movements in price, highlighted in orange, and apply those movements as demonstrated by the red cells in worksheet aac.ax. The yellow dates are the future dates.


In this example I have limited it to two worksheets, but there could be many more worksheets for excel to look through. All worksheets follow the same naming format. "***.ax"


I hope I have explained what I am trying to achieve.


Thank you for your assistance


Cheers


Shaun

 

[SirJB7]

Hi, Shaun!


Giving a look to your uploaded file I don't find any correlation between % volume movements on worksheet aac.ax highlighted in green and on worksheet abc.ax highlighted in purple. Here they are:

[pre]

0,420577617	0,428317008
0,621773288	0,62493044
1,216382253	1,214189189
0,634473798	0,636833046
1,770705521	1,761940864
0,500191791	0,503051106
2,367847411	2,349462366
1,586455331	1,574047955
0,103304555	0,105302243
54,61788618	48,78985507
0,011625709	0,013025012
40,53639847	38,38768116
0,041540665	0,043823436
4,339088398	4,304853042
1,297491039	1,293545535
1,508108108	1,498013245
0,85155351	0,854072398
0,124910162	0,126792886
4,367231638	4,335820896

[/pre]
BTW, assuming there's a -still undiscovered- logical criteria while identifying purple range, how do you switch to orange range? Criteria?


Regards!

 

[Shaun]

Hi all


The relationship is between volume movements between

* aac.ax - 01 Feb 2012 and 27 Feb 2012 (19 workdays); and

* abc.ax - 03 Oct 2011 and 27 Oct 2011 (19 workdays).


I have prepared a line graph to demonstrate the relationship.


The volume movements for aac.ax between 01 Feb and 27 Feb are similar to the movements of abc.ax between 03 Oct and 27 Oct. The green and purple data are the % movements (today's volume / yesterday's volume). I thought that this would be the easiest "string" for Excel to compare and find against other historical data.


Effectively what I am saying is I want Excel to find a similar volume pattern (green) in the historical data available (purple).


When it finds similar movements (give or take 5%) then use the price movements (today's close price / yesterday's close price) for the same period looking forward (19 workdays in this case) commencing 28 Oct 2011 (orange data) then use those percentages to "predict" the close price of each days close price in the future (yellow and red):

* 27 Feb 2012 close price (Actual) was 1.38;

* 28 Feb 2012 close price (Predicted) is calculated by 1.38 (aac.ax!B5) * 0.989761 (abc.ax!E87) = 1.36587 (aac.ax!G23)

* 29 Feb 2012 close price (Predicted) is calculated by 1.36587 * 0.989655 = 1.351741

* 01 Mar 2012 close price (Predicted) is calculated by 1.351741 * 1 = 1.35741

and so on.


New file with graph:


https://rapidshare.com/files/165366559/Example2.xlsx


I apologise for any confusion.


Cheers


Shaun