Categories
Calculate Expected Shortfall in SQL
August 20th, 2015 under Programming. [ Comments: none ]

A commonly used risk measure is the Expected Shortfall. Unfortunately, this function to calculate the sample expected shortfall is rarely implemented. As there is much data stored in databases accessible through SQL, I tried to calculate this value directly in SQL. It turns out this is a one-liner. Let’s assume we have a table called “LossSimulation” and a column/field named “LossValue” then the sample expected shortfall at level 95% is

 ```1 ``` `SELECT AVG(LossValue) FROM (SELECT TOP 95 PERCENT LossValue FROM LossSimulation ORDER BY LossValue)`
`SELECT AVG(LossValue) FROM (SELECT TOP 95 PERCENT LossValue FROM LossSimulation ORDER BY LossValue)`

Note: Microsoft Access may list more than 95% of all records because if multiple value at the 95% quantile value are the same then all these records are listed (see here).

Unconstrained Optimization
August 13th, 2015 under Programming. [ Comments: none ]

MATLAB/GNU Octave offers simple ways to find the minimum of any function. The two easiest methods are

 ```1 ``` `fminsearch`
`fminsearch`
and
 ```1 ``` `fminunc`
`fminunc`
. The former uses a simplex search method while the latter uses a gradient based method.

Here is a simple example for a lognormal parameter estimation:

 ```1 2 3 4 ``` ```x = sort([0.0027400 0.0093730 0.0458460 0.0135300]) y = (1:length(x))./(length(x)+1) fun = @(p) sum( (logninv(y,p(1),p(2)) - x).^2) estimate = fminsearch(fun,[-5;2])```
```x = sort([0.0027400 0.0093730 0.0458460 0.0135300])
y = (1:length(x))./(length(x)+1)
fun = @(p) sum( (logninv(y,p(1),p(2)) - x).^2)
estimate = fminsearch(fun,[-5;2])```

ffmpeg stream interleaving
October 24th, 2012 under Programming. [ Comments: none ]

There are plenty of complaints about the ffmpeg API being badly documented and hard to learn. While this was the case some years ago, ffmpeg has come along well and is very well documented. However, there are still some parts where prior knowledge is required and thus looks not well documented. For me, this was the case with writing video files.

First let’s look at what we need to do to write a video:
1) find you frame you want to add to the video.
2) bring it into a form the encoder can work with (I use here the

 ```1 ``` `sws_scale`
`sws_scale`
which converts my RGBA to a YUV420p or whatevery the encoder likes)
3) encode this sws_scaled frame with
 ```1 ``` `avcodec_encode_video2`
`avcodec_encode_video2`
. This gives us an
 ```1 ``` `AVPacket`
`AVPacket`
when the frame was encoded. It can also be buffered, in which case no valid AVPacket is returned. When you want to finish you video and you last frame was buffered then you need to flush the encoder to give you all the buffered images.
4) when a valid
 ```1 ``` `AVPacket`
`AVPacket`
is returned then it need to be written to the video file. This is done by either
 ```1 ``` `av_write_packet`
`av_write_packet`
or
 ```1 ``` `av_interleaved_write_packet`
`av_interleaved_write_packet`
.

In step 4 I was not sure what to choose. What does interleaved mean? What are the consequences of using the wrong function?

So, here is what I figured you.

Interleaving means the interleaving of the individual streams in a video file. A video normally, has one video and one audio stream. For a video player to efficiently play the audio and video frames for a certain point in time the data should be close by (in a memory sense). Otherwise, the player has to hunt and seek the matching pairs of audio and video frames over the entire video file. This of course would kill the real-time aspect of any video. So to provide a sensible video we must interleave the audio and video frames. When we look at how ffmpeg interleaves a simple matroska example we see that the packets in the video file are first ordered by timestamp and then by stream index. Of course when the video has not only I Frames this timestamp ordering is not given.

 ```1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 ``` ```I frame, track 1, timecode 0 (00:00:00.000), size 1028, adler 0x8c9b270f I frame, track 3, timecode 0 (00:00:00.000), size 1028, adler 0x8c9b270f P frame, track 1, timecode 167 (00:00:00.167), size 48, adler 0x9881050a I frame, track 2, timecode 0 (00:00:00.000), size 343, adler 0xcc5f05be P frame, track 3, timecode 167 (00:00:00.167), size 48, adler 0x9881050a I frame, track 4, timecode 0 (00:00:00.000), size 343, adler 0xcc5f05be I frame, track 2, timecode 21 (00:00:00.021), size 343, adler 0xc34e03ff I frame, track 4, timecode 21 (00:00:00.021), size 343, adler 0xc34e03ff P frame, track 1, timecode 42 (00:00:00.042), size 47, adler 0x76da04a0 I frame, track 2, timecode 42 (00:00:00.042), size 342, adler 0xb5f409b0 P frame, track 3, timecode 42 (00:00:00.042), size 47, adler 0x76da04a0 I frame, track 4, timecode 42 (00:00:00.042), size 342, adler 0xb5f409b0 I frame, track 2, timecode 63 (00:00:00.063), size 343, adler 0xcf892379 I frame, track 4, timecode 63 (00:00:00.063), size 343, adler 0xcf892379 P frame, track 1, timecode 83 (00:00:00.083), size 47, adler 0x798504b1 P frame, track 3, timecode 83 (00:00:00.083), size 47, adler 0x798504b1 I frame, track 2, timecode 85 (00:00:00.085), size 342, adler 0xc1533251 I frame, track 4, timecode 85 (00:00:00.085), size 342, adler 0xc1533251 I frame, track 2, timecode 106 (00:00:00.106), size 343, adler 0x58ee3264 I frame, track 4, timecode 106 (00:00:00.106), size 343, adler 0x58ee3264 P frame, track 1, timecode 125 (00:00:00.125), size 47, adler 0x7c3204c4 P frame, track 3, timecode 125 (00:00:00.125), size 47, adler 0x7c3204c4 I frame, track 2, timecode 127 (00:00:00.127), size 342, adler 0xe6cd388f I frame, track 4, timecode 127 (00:00:00.127), size 342, adler 0xe6cd388f I frame, track 2, timecode 148 (00:00:00.148), size 343, adler 0x840b446a I frame, track 4, timecode 148 (00:00:00.148), size 343, adler 0x840b446a```
```I frame, track 1, timecode 0 (00:00:00.000), size 1028, adler 0x8c9b270f
I frame, track 3, timecode 0 (00:00:00.000), size 1028, adler 0x8c9b270f
P frame, track 1, timecode 167 (00:00:00.167), size 48, adler 0x9881050a
I frame, track 2, timecode 0 (00:00:00.000), size 343, adler 0xcc5f05be
P frame, track 3, timecode 167 (00:00:00.167), size 48, adler 0x9881050a
I frame, track 4, timecode 0 (00:00:00.000), size 343, adler 0xcc5f05be
I frame, track 2, timecode 21 (00:00:00.021), size 343, adler 0xc34e03ff
I frame, track 4, timecode 21 (00:00:00.021), size 343, adler 0xc34e03ff
P frame, track 1, timecode 42 (00:00:00.042), size 47, adler 0x76da04a0
I frame, track 2, timecode 42 (00:00:00.042), size 342, adler 0xb5f409b0
P frame, track 3, timecode 42 (00:00:00.042), size 47, adler 0x76da04a0
I frame, track 4, timecode 42 (00:00:00.042), size 342, adler 0xb5f409b0
I frame, track 2, timecode 63 (00:00:00.063), size 343, adler 0xcf892379
I frame, track 4, timecode 63 (00:00:00.063), size 343, adler 0xcf892379
P frame, track 1, timecode 83 (00:00:00.083), size 47, adler 0x798504b1
P frame, track 3, timecode 83 (00:00:00.083), size 47, adler 0x798504b1
I frame, track 2, timecode 85 (00:00:00.085), size 342, adler 0xc1533251
I frame, track 4, timecode 85 (00:00:00.085), size 342, adler 0xc1533251
I frame, track 2, timecode 106 (00:00:00.106), size 343, adler 0x58ee3264
I frame, track 4, timecode 106 (00:00:00.106), size 343, adler 0x58ee3264
P frame, track 1, timecode 125 (00:00:00.125), size 47, adler 0x7c3204c4
P frame, track 3, timecode 125 (00:00:00.125), size 47, adler 0x7c3204c4
I frame, track 2, timecode 127 (00:00:00.127), size 342, adler 0xe6cd388f
I frame, track 4, timecode 127 (00:00:00.127), size 342, adler 0xe6cd388f
I frame, track 2, timecode 148 (00:00:00.148), size 343, adler 0x840b446a
I frame, track 4, timecode 148 (00:00:00.148), size 343, adler 0x840b446a```

I still need to figure out the rule for interleaving the P Frames properly.

So back to the question what the consequences are of using the wrong function.

 ```1 ``` `av_write_packet`
`av_write_packet`
expects the programmer to pass the packets in the interleaved order. As we can see this can be difficult when P Frames and B Frames come into play. On the other hand,
 ```1 ``` `av_interleaved_write_packet`
`av_interleaved_write_packet`
buffers the packets internally and reorders then such that they meet the “interleaved criteria”. So, in case of doubt about how the correctly interleave (which I obviously still am) use the
 ```1 ``` `av_interleaved_write_packet`
`av_interleaved_write_packet`
. This guarantees you the right interleaving. On the downside it requires more memory due to the internal buffering. This buffering can be enormous when the packets are passed to
 ```1 ``` `av_interleaved_write_packet`
`av_interleaved_write_packet`
in a suboptimal way. For instance, writing first all frames from stream one the all frames from stream two is bad because the first stream needs to be buffered entirely.

To sum up, as I still do not know how to interleave P and B Frames properly I will use the

 ```1 ``` `av_interleaved_write_packet`
`av_interleaved_write_packet`
to write video files. When not one entire stream after the other is written (all packets of stream one then all packets of stream two and so on) the performance is acceptable and the memory consumption moderate.

CMake and Compiler target architecture
October 17th, 2012 under Programming. [ Comments: none ]

In a windows build system I use, we set up two distinct development environments. Both use the MinGW-w64 compilers. One is the 32 and the other the 64 bit GCC compiler. In addition I use CMake as the build system. Now I need to know if I’m compiling for 32 or 64 bit to know against which libs to link. On way of figuring this out is exploiting that we do not crosscompile. Depending on the environment I have the 32 or 64 bit GCC compiler. GCC tells you what its target machine is either with the

 ```1 ``` `-v`
`-v`
option or the
 ```1 ``` `-dumpmachine`
`-dumpmachine`
option. So this can easliy be included in CMake and find the appropriate libraries

INT*_C Macros
September 11th, 2012 under Programming. [ Comments: none ]

 ```1 ``` `stdint.h`
`stdint.h`
defines loads of macros. As such it is an omnipresent header that is included in many other headers. Being a C99 header, some macros are disabled in C++. One example are the
 ```1 ``` `INT8_C, INT16_C, INT32_c and INT64_C`
`INT8_C, INT16_C, INT32_c and INT64_C`
macros. When looking at the header (see below) one sees that the macros can be enabled in C++ by defining
 ```1 ``` `__STDC_CONSTANT_MACROS`
`__STDC_CONSTANT_MACROS`
.

 ```1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 ``` ```/* The ISO C99 standard specifies that in C++ implementations these    should only be defined if explicitly requested.  */ #if !defined __cplusplus || defined __STDC_CONSTANT_MACROS   /* Signed.  */ # define INT8_C(c)      c # define INT16_C(c)     c # define INT32_C(c)     c # if __WORDSIZE == 64 #  define INT64_C(c)    c ## L # else #  define INT64_C(c)    c ## LL # endif   ... #endif```
```/* The ISO C99 standard specifies that in C++ implementations these
should only be defined if explicitly requested.  */
#if !defined __cplusplus || defined __STDC_CONSTANT_MACROS

/* Signed.  */
# define INT8_C(c)      c
# define INT16_C(c)     c
# define INT32_C(c)     c
# if __WORDSIZE == 64
#  define INT64_C(c)    c ## L
# else
#  define INT64_C(c)    c ## LL
# endif

...
#endif```

When i tried this in some C++ file in my project, it didn’t fix the

 ```1 ``` `error: ‘UINT64_C’ was not declared in this scope`
`error: ‘UINT64_C’ was not declared in this scope`
. I realized that the header has already been included before I defined
 ```1 ``` `__STDC_CONSTANT_MACROS`
`__STDC_CONSTANT_MACROS`
. Due the the header guard the define has no effect. To fix this problem I simply undef the header guard.

 ```1 2 3 4 5 ``` ```#define __STDC_CONSTANT_MACROS #ifdef _STDINT_H # undef _STDINT_H #endif # include ```
```#define __STDC_CONSTANT_MACROS
#ifdef _STDINT_H
# undef _STDINT_H
#endif
# include <stdint.h>```

Of course this trick does not work for all header but in the case of

 ```1 ``` `stdint.h`
`stdint.h`
it works because this header only defines macros.

Flickr

View All Photos

Recent Posts
Quotes
“Vanessa Loring: Your parents are probably wondering where you are.
Juno MacGuff: Nah... I mean, I'm already pregnant, so what other kind of shenanigans could I get into?”