Geometric Annual Return in SQL

Here is some quick-and-dirty SQL to calculate an geometric annual return (as a percent) from a column of monthly returns (in percents).

/* Convert the annualized number back to a percent */
SELECT (T3.AnnHPR - 1) * 100 AS GeomAnnRet
FROM
  (
  /* Annualize the holding period return */
  SELECT POWER(T2.HPR, 12.0 / T2.NumReturns) AS AnnHPR
  FROM
    (
    /* Calculate the holding period return over the time
       period.
       
       POWER(10, SUM(LOG10(n))) is a simulated PRODUCT(n)
       aggregate function.
       
       The precision of POWER is determined by the precision
       of the first argument, so use a lot of decimals. */
    SELECT POWER(10.0000000000000000,
                 SUM(LOG10(T.MonthReturn))) AS HPR,
           COUNT(*) AS NumReturns
    FROM
      (
      /* Convert all percent returns to multipliers (1% ->
         1.01) */
      SELECT 1 + MonthPctReturn / 100 AS MonthReturn
      FROM ...
      ) AS T
    ) AS T2
  ) AS T3

Update 2008-01-30 10:52PM: Here’s the equivalent “one-liner”:

SELECT 100 * (POWER(POWER(10.000000000000000,
                          SUM(LOG10(1 + MonthPctReturn / 100))),
                    12.0 / COUNT(*)) - 1)
FROM ...
Advertisements

STL Objects and Win32 Module Boundaries

Let’s say you have the following function:

void AppendChar(std::string& s, char ch)
{
    s += ch;
}

What happens if this function is exported as an ordinal function from a DLL (not an inlined piece of code inside a header) and you call it from an EXE?

It works most of the time. When it doesn’t, it corrupts your heap and causes a spectacular mess.

In Windows you must free memory with the same allocator that allocated it. However, your EXE may not share the same allocator as the DLL. Perhaps the two modules are linked against different versions of libc, or perhaps one of the modules is using a static version of libc. If your EXE and DLL do not share an allocator and if AppendChar resizes the string s, you will almost certainly cause a heap corruption.

The STL performs a lot of reallocations behind the scenes for you; this is one of its major benefits. Unfortunately, if you are writing a general-purpose DLL these behind-the-scene allocations are deadly. You cannot know or dictate what version of libc your clients will use.

Therefore, I reiterate my previous recommendation:

Avoid passing STL objects as parameters to DLLs.