Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.soft-sys.math.mathematica > #3228 > unrolled thread

Date based calculations

Back to article view | Back to comp.soft-sys.math.mathematica

Contents

  Date based calculations Priyan Fernando <priyan.fernando@gmail.com> - 2011-06-22 07:43 +0000
    Re: Date based calculations Armand Tamzarian <mike.honeychurch@gmail.com> - 2011-06-23 11:28 +0000

#3228 — Date based calculations

I have the following time series data (19 rows by 4 columns):

{{"03-May-11", -0.0400698, -0.00671613, -0.0333613}, {"04-May-11",
  0, -0.00355872, -0.0169521}, {"05-May-11", 0.0453721,
  0.0128572, -0.0248498}, {"06-May-11", 0.0052084, -0.00317346,
  0.00290203}, {"09-May-11", -0.0155441, 0.0254686,
  0.0597703}, {"10-May-11",
  0.0315789, -0.0051742, -0.0179181}, {"11-May-11", -0.0323129,
  0.00450769, -0.0346655}, {"12-May-11", 0,
  0.0200207, -0.0200701}, {"13-May-11", -0.00702991, 0,
  0.0400441}, {"16-May-11", 0, 0.00203048, -0.0121865}, {"19-May-11",
  0.0318584, 0.00979397, 0.0229752}, {"20-May-11",
  0.00343055, -0.00401342, 0.000611685}, {"23-May-11", -0.025641,
  0.0020148, -0.0259388}, {"24-May-11", -0.00526314, 0.00435663,
  0.00376576}, {"25-May-11", 0.0194003,
  0.00133465, -0.0125949}, {"26-May-11", -0.0190311,
  0.00533158, -0.00958927}, {"27-May-11", 0.0282187, -0.007955,
  0.0026488}, {"30-May-11", -0.0291595,
  0.00233883, -0.00291514}, {"31-May-11", 0.0212014, 0, -0.0383737}}


The 1st Column is a date while Columns 2,3,4 represent the returns of stocks
A,B and C respectively. Could you please help me extract information based
on the date. E.g. date1 and date2 are variables inputted by the user.

Example 1: Average returns of stocks A,B,C between 9-May-11 and 19-May-11
(dates inclusive) are:
0.001221494    0.008092462    0.005421325

Example 2: Variance/Covariance Matrix between of stocks A,B,C between
9-May-11 and 19-May-11 (dates inclusive) is
0.0004767002    -0.0000660161    0.0000041896
-0.0000660161    0.0001054570    0.0001357245
0.0000041896    0.0001357245    0.0010817457

This is easy to do in Excel, but I'm finding it hard to locate the correct
functions in Mathematica. Any suggestions are much appreciated.

Thank you.

[toc] | [next] | [standalone]

#3257

On Jun 22, 5:43 pm, Priyan Fernando <priyan.ferna...@gmail.com> wrote:
> I have the following time series data (19 rows by 4 columns):
>
> {{"03-May-11", -0.0400698, -0.00671613, -0.0333613}, {"04-May-11",
>   0, -0.00355872, -0.0169521}, {"05-May-11", 0.0453721,
>   0.0128572, -0.0248498}, {"06-May-11", 0.0052084, -0.00317346,
>   0.00290203}, {"09-May-11", -0.0155441, 0.0254686,
>   0.0597703}, {"10-May-11",
>   0.0315789, -0.0051742, -0.0179181}, {"11-May-11", -0.0323129,
>   0.00450769, -0.0346655}, {"12-May-11", 0,
>   0.0200207, -0.0200701}, {"13-May-11", -0.00702991, 0,
>   0.0400441}, {"16-May-11", 0, 0.00203048, -0.0121865}, {"19-May-11",
>   0.0318584, 0.00979397, 0.0229752}, {"20-May-11",
>   0.00343055, -0.00401342, 0.000611685}, {"23-May-11", -0.025641,
>   0.0020148, -0.0259388}, {"24-May-11", -0.00526314, 0.00435663,
>   0.00376576}, {"25-May-11", 0.0194003,
>   0.00133465, -0.0125949}, {"26-May-11", -0.0190311,
>   0.00533158, -0.00958927}, {"27-May-11", 0.0282187, -0.007955,
>   0.0026488}, {"30-May-11", -0.0291595,
>   0.00233883, -0.00291514}, {"31-May-11", 0.0212014, 0, -0.0383737}}
>
> The 1st Column is a date while Columns 2,3,4 represent the returns of sto=
cks
> A,B and C respectively. Could you please help me extract information base=
d
> on the date. E.g. date1 and date2 are variables inputted by the user.
>
> Example 1: Average returns of stocks A,B,C between 9-May-11 and 19-May-11
> (dates inclusive) are:
> 0.001221494    0.008092462    0.005421325
>
> Example 2: Variance/Covariance Matrix between of stocks A,B,C between
> 9-May-11 and 19-May-11 (dates inclusive) is
> 0.0004767002    -0.0000660161    0.0000041896
> -0.0000660161    0.0001054570    0.0001357245
> 0.0000041896    0.0001357245    0.0010817457
>
> This is easy to do in Excel, but I'm finding it hard to locate the correc=
t
> functions in Mathematica. Any suggestions are much appreciated.
>
> Thank you.

data = your data above.

You need to select using absolute times

Select[data, AbsoluteTime["09-May-11"] <= AbsoluteTime[#[[1]]] <=
AbsoluteTime["19-May-11"] &]

or

Cases[data, x_ /; AbsoluteTime["09-May-11"] <= AbsoluteTime[x[[1]]] <=
AbsoluteTime["19-May-11"]]

If this is going to be a regular operation it is probably best (i.e.
faster) to create a 5th column that contains the absolute time of the
date.

newData = Transpose@Join[Transpose[data], {AbsoluteTime /@ data[[All,
1]]}]

start = AbsoluteTime["09-May-11"];
finish = AbsoluteTime["19-May-11"];

Cases[newData, x_ /; start <= x[[5]] <= finish]

once you have done the extraction the additional operations look to be
straight forward.

Mike

[toc] | [prev] | [standalone]

Back to top | Article view | comp.soft-sys.math.mathematica

csiph-web