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])```

Last value of a data range
June 25th, 2015 under Excel. [ Comments: none ]

I was faced with the problem ofÂ finding the last value of a data range in Excel. Assume the range resides in the column A. Today there are 50 values in A1:A50. Over the time new values will be added and the goal is to get the most recent (the last) value.

The solution is the INDEX() function. It allows you to select a value in a range by index. Therefore, the following solves my issue if I know there won’t be more than 100 values

 ```1 ``` `INDEX(A1:A100;COUNT(A1:A100))`
`INDEX(A1:A100;COUNT(A1:A100))`

Otherwise

 ```1 ``` `INDEX(A:A;COUNT(A:A))`
`INDEX(A:A;COUNT(A:A))`
gives the answer as long as there aren’t any empty cells between the values.

About Motivation
February 2nd, 2013 under This And That. [ Comments: none ]

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.

Flickr

View All Photos

Recent Posts
Quotes