-
Important update on 16. February 2019: Some links, and especially downloads links on this personal website may not be uptodate, as I recently transferred the whole site from www.finaquant.com to software.tuncalik.com.
Give me some time; I will check and correct them one by one.
I decided to offer all the software introduced on this website open-source and free.
Check my shared folder for most uptodate downloads. Open-Source downloads of the C#/.NET libraries Finaquant Calcs and Finaquant Protos are also included in this shared download folder as zipped Visual Studio 2012 files.
You may contact me if you need any help or consulting for my analytical software.
Follow me
-
Recent Posts
- Minimum Total Loss Rounding in Python
- Travel and Assignment Planning Software in Python
- Top-Down Distribution of Quality Measurement Requirements using Table Functions in Python
- A Simple Notation for Data Tables
- Calculating Sales Commissions in Excel
- How to Add a User-Defined Table Function to Excel
- How to Apply a User-Defined Function on Rows of a Table in Excel
- Empowering Excel with Table Valued Functions
- Testing Table Valued Functions with LINQPad
- Building a Web Service that Receives and Returns Data Tables
- Optimizing Marketing Investment
- Reporting with ReportViewer in Visual Studio
- Simulation of Dealer Commissions with Matlab
- Finaquant’s Matrix and Vector Functions
- Table Function Demo with Microsoft Excel
Recent Comments
No comments to display
Video: Rajdeep Sardesai talks to Tiger conservationist Valmik Thapar about the rise in the population of tigers in India. 28. March 2011
Watch the video at
http://ibnlive.in.com/videos/147400/tiger-count-up-a-good-day-for-the-big-indian-cat.html
Fateh Singh Rathore, the ‘Tiger Guru’ dies at 73. New York Times, 8. March 2011
http://www.nytimes.com/2011/03/09/world/asia/09rathore.html
Thank you for this article. I found it interesting though I have to admit that I couldn’t follow the whole argument. As a controller working at an insurance company I am very much involved in reporting issues. What we do is, we first transfer all the available data from operations to the business datawarehouse, than think what reports we could generate with these data. My question is, what is the use of this consolidation method in such a context?
Your company has really an interesting approach to reporting. It reminds me one episode of the brilliant BBC television series “Yes Minister”, where the civil servant Sir Humphrey informs the Minister about the government’s tax policy: ” We first collect as much as we can, then we start to think how to spend them”. Coming back to reporting, no, in your case this consolidation concept is not very useful. It could only be useful for requirement-driven reporting environments, where you need only a relevant subset of vast operational data to generate the required reports.
Thank you for this overview and the downloadable slides. One thing I didn’t quite understand: Is trailer fee a harmless passthrough payment as a sort of revenue share, or a hidden cost to investor? Another question: Do you know what “payout rate” means? This is a term I often see in the fund business.
Whether the trailer fee paid to intermediaries is a hidden cost or not depends on how the payment is financed. For example, if the distributor (principle underwriter) pays the trailer fees with a portion of official 12b-1 fees it receives annually from the fund, this won’t be a hidden cost because 12b-1 fees are declared in the fund’s prospectus and the pass-through trailer fee payment does not incur an additional cost to shareholders. But if the trailer fees are paid from the fund without an explicit declaration to shareholders, this will be a hidden cost, because shareholders will be paying for the trailer fees indirectly from their fund assets without being aware of it.
The term “payout” can have different uses, but one use I know is the share of trailer fee received by a intermediary which is passed to the financial adviser of the clients, that is, shareholders.
Thanks a lot for this comprehensive summary. Could you please clarify position of performance fees in mutual funds fees. Are they an alternative to transactional fees or management fees? I’ll be glad if you can explain further, different mutual fund types, in one of your next posts.
Thanks & Regards
My recent article “Simple Performance Fee Calculation” that I published possibly after your comment explains in one section the use of performance fees in the investment fund industry. Summarized shortly, performance fees are quite common among hedge funds, but only a small minority of mutual funds, about 3% of retail funds in US, use performance fees for the remuneration of their investment managers. Most mutual funds pay only management fees for investment management.
In most cases, performance fees are paid in combination with a management or basis fee. The rate of management fees are naturally reduced if there is an additional performance fee for the investment advisery. Typical hedge fund rates are about 2% annually for annual management fees (applied on assets under management) and 20% annually for performance fees. Note that hedge funds are high risk investments with much larger expected returns compared to mutual funds.
Hi Tunc,
Thanks a lot for useful information. But I am a bit confused, because i couldn’t get exactly interaction between NAV and no of shares, in your Individual HWM approach.
Your solution depends on a single NAV value for a specific time, if i understood correctly. According to this assumption, no of shares should be variant.
Let me give you a concrete example;
total value of portfolio : 1000 chf
total number of shares : 100
NAV : 10 chf
share holder 1 : 25 shares
share holder 3 : 50 shares
share holder 3 : 25 shares
share holder 4 (new) : makes a subscription of 1000 chf
I suppose that NAV shouldn’t change, so new share holder will have 100 shares and new total number of shares will be 200?
If total number of shares remains same, then NAV will change and without any performance, portfolio manager will gain non deserved benefit.
Am I right? This is how it is intended to be?
Thanks & Regards
Hi Ugur, if your question is about the share redistribution logic at the end of each period, I plan to write about the mathematical details of individual HWM method in a future related article. I hope, the redistribution logic will then become more clear.
Generally, total number of fund shares don’t remain constant. As you wrote, the number of shares will increase if more shares are sold (i.e. subscribed) than redeemed. The calculations are generally done in terms of NAV per share (NAVps) to obtain the final result performance fee per share.
There is a share redistribution process at each period-end to satisfy general equalization requirements, as mentioned in the article:
1) Fees are allocated fairly and equitably among investors
2) The is a single and common NAV per share value
3) NAV per share accurately reflects the fund’s performance
The share redistribution is calculated such that the total number of shares before and after the redistribution is the same, to satisfy the third requirement above.
I used the term “rule based” for the calculation framework I introduced here. This term shouldn’t be confused with the “Business Rules Framework”. Maybe I shouldn’t use it at all to avoid confusions, and say just “calculation framework, or engine”.
With “rule based” I mean essentially fully configurable. There is another reason why I used the term; the function block named “function router” based on a decision matrix. With the decision matrix, which is a parameter of the function router, you decide which functions are to be applied on which data sets. In that sense, you set the rules which functions are to be executed for which data sets.
You could for example configure the framework so that investment performance measurement method M1 is applied for fund group G1, method M2 is applied for fund group G2, and so on.
Business idea: XXX Analytics for Business and Science
Because I worked in the financial services industry as senior consultant with pre-sales support for a couple of years, I have first-hand experience that many fund companies invest hundreds of thousands of dollars for the implementation of some fee calculations for invoice generation and reporting. These are typically expensive, lengthy and tiresome IT projects that can continue for several quarters, in some cases even years. The time frame and costs are typically double or triple of the amounts planned initially. The software solution ultimately developed is in most cases too complex and entangled, accordingly expensive to maintain, because it has grown in an ad-hoc fashion without solid analytical models.
Why is this so? Why do some asset management companies continue to invest hundreds of thousands in fee calculations? Is there no other solution?
Yes, there is another solution. All fee calculation projects (and similar projects in other industries as well) could be completed much faster, better and economical with a complete calculation framework, as explained in the article above, provided that solutions for different areas like calculations, reporting and workflow are separated with well-defined interfaces among them.
I haven’t yet seen a complete calculation framework in the market as explained in the article above. Maybe there are some, of which I am not yet aware. All the solutions I have seen so far under names like calculation engine are not real calculation frameworks based on analytical models. If there was a real calculation engine with well-defined interfaces to reporting and workflow, fund companies wouldn’t need to invest so much in fee calculations.
Even if there is a real calculation engine in the market, well hidden somewhere outside the knowledge and appreciation of potential customers, this doesn’t mean that a better one can’t be produced.
I am currently working on the prototype of the calculation framework, which will be ready for demonstrations within a couple of months. We plan to convert this prototype into a product with some competent business partners from the software industry.
We also have a business idea for this product: XXX Analytics for Business and Science. This will be the name of the company which will develop and sell the product.
The current version of the business idea foresees a tree-phase deployment plan:
An initial public offering (IPO) may or may not be realized after the second phase, depending on the size of the customer base, and other factors.
Please don’t hesitate to contact us if you are interested in this business idea as a potential stakeholder; as investor, software developer, analyst or any kind of business partner.
Note that this project needs rational and independent minded investors that are real trend setters; not trend surfers. Innovative and creative marketing strategies will be very important for especially the second phase of the business deployment.
Other key points for XXX Analytics for Business and Science:
Tunç Ali Kütüçüoglu
Matrix determinant function is added to the new version V1.2 (July 2012). All the VBA scripts published so far for generating test data (as all possible attribute value combinations) can be found in Module3 of the downloadable excel file.
Tiger Kills Zoo Keeper While Trying to Escape
http://news.softpedia.com/news/Tiger-Kills-Zoo-Keeper-While-Trying-to-Escape-288222.shtml
German:
Tiger tötet Pflegerin
http://www.spiegel.de/panorama/gesellschaft/koelner-zoo-tiger-toetet-pflegerin-a-852050.html
Hi,
thanks very much for these functions.
I’m not a very experienced vba user, and the functions included in the file provided by Finaquant, helped me a lot in a project where I had to use matrix operations extensively.
Keep up the good work
The launch of the commercial calculation engine finaquant® calcs will be delayed a couple of months into the next year 2013.
Its launch was originally planned for the end of 2012. The reason of this delay is finaquant® protos, the free (zero-priced) non-commercial and smaller version of finaquant® calcs, which will be available for download within a couple of weeks. We want to collect some test results and feedbacks for finaquant® protos first before launching our first commercial product finaquant® calcs.
Hi Tunc
Quite impressive work thanks for sharing.
If i understood correctly, with your new library, you are bringing a new alternative to development environments like (SQL-DB), (matlab-matrix operations) so this peace of code, only needs Visual Studio and your library to manage such operations, like join, aggregation etc…
If so, apart having possibility to use table/matrix/array level operations without any dependency to specific DB’s (oracle, SQLServer, DB2 etc…) or tools/environments (R, Mathlab etc…), your library brings any further concrete advantages, to consider it much more convenient than it’s alternatives or you mainly you tried to bring a compact, chip replacement, to these relatively difficult to maintain and expensive commercial solutions?
Thanks & Regards
Hi Ugur,
Yes, table calculations directly within the .NET framework, with in-memory data is one aspect of our calculation engines. These table functions are the infrastructure of an upcoming real calculation engine based on table functions.
The other aspect will be features like ability to define and maintain network calculations with multiple chained nodes (or contracts), simplified parameter management etc. that will be built on this infrastructure. Only these additional features will make our products real calculation engines.
I am also a proficient user of math software like matlab or R. There are cases where you want to make operations with table data. Converting these tables first to matrices, making the operations, and then everything back to tables is an error-prone and tedious task. Table functions offer a more direct and intuitive way of doing operations on tables. Once (and if) you get used to working with tables as a matrix guru, you will understand what I mean; you won’t want to go back to matrices for structured higher-level table data unless it is strictly necessary.
I haven’t seen so far a software (maybe there are some, but I coudn’t find any) including stored procedures of certain databases, which offers table functions in the entirety and with the mathematical clarity as finaquant intends to do. I have seen some patches table functions embedded in expensive software for applications like financial planning and reporting, but they are not extensive, complete, and well-defined (in the mathematical sense) enough for users with some analytical flair, who want to configure (or program) their calculations themselves without much reliance on external support.
Regards, Tunc
Two new video films from National Geographic are added to the links for tigers: Tiger Prey and Tiger Terror.
Hi,
I am trying to download this spreadsheet to do some comparisons with some code I have written but when I click on it it looks like the download does not exist. At least this is the message I get. Any ideas why it is doing that?
Thanks a lot
Laki
Hello Laki, apparently there was a temporary configuration error in the download manager which is then corrected. Downloads should be working by now. Thank you for your warning.
Interesting comparison: Tiger versus Lion.
http://www.diffen.com/difference/Lion_vs_Tiger
http://en.wikipedia.org/wiki/Tiger_versus_lion
There is a slight change in the product road map. There won’t be a separate product named finaquant® frames. All its planned calculation engine features will be incorporated into the upcoming commercial product finaquant® calcs.
A very well written article defining “Calculation Engine” in the simplest idea.
The reader must have some basic knowledge of how mathematical functions work and also knowledge related to terms like, Stored Procedures; Tables; Database; Data Structure; is also required.
Can we type cast MatrixTable class of finaquant to a DataTable class of .NET &/or vice versa?
Casting no, but there are import and export functions for conversions.
By using the MatrixTable method
Export_To_DataTable()any MatrixTable object can be converted to a DataTable object.A DataTable can be converted to a MatrixTable with the MatrixTable method
Import_from_DataTable(). All the fields of the DataTable must be predefined in MetaData with proper field types (text attribute, numeric attribute of type integer or date, or key figure) before applying this conversion.Function router can be a very useful function for those who spend hours and hours for converting some parts of tables into matrices on a case-by-case basis like myself. I often use R for matrix computations and I would like to see an example how function router can be integrated with R, as you mention in the article.
Hello selmar, you can find an example in the related article: Table data as input to estimation functions in R – How can we apply estimation functions in R on selected parts (i.e. subtables) of an input table with historical data http://software.tuncalik.com/table-data-as-input-to-estimation-functions-in-r/3082
In the release 1.03 of finaquant protos all the import/export functions related with excel files are replaced by their CSV file (Comma Separated Values) counterparts. The users of finaquant protos can export/import tables to/from CSV files with semicolon as the delimiter character. These CSV files can be opened with MS excel or similar open source applications.
I couldn’t understand how cost variation is calculated in example of “Aggregate all key figures w.r.t. all attributes with user-defined aggregation operation”.
Hello swapnil, you are perfectly right in not understanding the function, because a critical information was missing; namely the input table “before aggregation”. We added this input table to the page. Note that without the field “year” we obtain recurring attribute combinations like Computer-HP and Computer-Asus. The aggregation is done for each attribute pair. For example, we get zero variation for the attribute pair Computer-HP because its cost is the same (100) for both pairs.
thanks
By chance I was working in a project for excel integration, this article helped a lot. Thanks.
One question: What is releaseObject() in ReadTableFromExcelRange()? If this is a method it is maybe missing in the code. Selina
Hello Selina, yes you are right. This piece of code was intially forgotton. The required helper method releaseObject() is then appended to the code for ReadTableFromExcelRange().
Thank you for the source code for excel integration. A question: What is the difference of matrixtable compared to datatable? What do I need the matrixtable for?
A MatrixTable has a simplified data structure compared to a DataTable with primary focus on analytical table functions like table addition, table multiplication, table combination, filtering, sampling and so on.
For more information you may visit:
http://software.tuncalik.com/converting-a-matrixtable-to-a-datatable-and-vice-versa/3452
and
http://software.tuncalik.com/help-pages-for-finaquant-products/text-and-numeric-attributes-key-figures
Calculation engine with multiple calculation instances is designed to work with a relational database, right? Can it also work with an excel file?
You may visit our facebook page for more information about Calculation Nodes and Networks: https://www.facebook.com/finaquant
I’m not sure why but this site is loading extremely slow for me. Is anyone else having this issue or is it a issue on my end? I’ll check back later on and see if the problem still exists.
It’s really a nice and helpful piece of information. I am happy that you just shared this useful information with us. Please keep us informed like this. Thanks for sharing.
You van create dataset manually … http://csharp.net-informations.com/dataset/csharp-dynamic-dataset.htm C# Dataset dynamic creation.
Steve.
You may visit https://datatableextensions.codeplex.com/ for DataTable extensions that include C# code of methods for importing/exporting tables from/to Excel sheets. Related methods are in the code file named DataTableExtensions.cs (under Source Code).
Hi Tunc,
excel macro sample is quite useful, but i couldn’t figure out, “why you preferred to add library functions, which get directly, excel file name or sheets etc… as input parameters?”
i would rather expect to see, a sample code, which shows how to call your library, with vbscript for example;
This code suppose to handle, creation of the library objects and their usages, combined with similar call to excel, word, access etc…
Is there any special user requirement, use case etc… behind this functionality?
Is it also possible to give an example like the one i have tried to explain above?
Shortly, i am expecting something like the code below. Of course, instead of IE, i want to create objects related to your library and call your methods…
Thanks & Regards
Sample vbscript code:
Set objIE = CreateObject(“InternetExplorer.Application”)
objIE.Visible = 1
objIE.Navigate “http://www.google.com”
Do Until VarType(objIE) = 9
WScript.Sleep 1000
Loop
Set objIE = Nothing
Hi Ugur, my answer is here in forum: http://forum.finaquant.com/viewtopic.php?f=4&t=1275
Hi Tunc – seriously great post….
It strikes me that in the simple model, as there are to be no subs/reds within a measurement period, the measurement period must be given by the NAV Period. I.e. if one’s dealing/trading day is a Friday (and thus that is when a new NAV is published), then the measurement period has to be fri-thurs. In other words, one does not *select* the measurement period – it must be inferred (from the NAV period).
Do you agree?
Thank you,
Tamim.
Tutorial: Adding a new table function to Excel add-in, step by step
http://forum.finaquant.com/viewtopic.php?f=4&t=1281
Yes, you are right. This is the first assumption of this simple performance model:
There are no inter-period share purchases (subscriptions) or sales (redemptions). All purchase or sale transactions occur at the beginning or end of the measurement periods.
New beta release (R102) includes Matrix Functions in addition to the primary feature of the excel add-in (Table Valued Functions). More information about Matrix Functions with developer notes can be found at:
http://forum.finaquant.com/viewtopic.php?f=9&t=1282
New beta release (R103) of Excel add-in for Table and Matrix functions comes with more refined and practical parameter forms:
https://www.facebook.com/finaquant/posts/707716322632587
Hi Tunc
As far as i have understood, with this xls add-in, we will have a set of mathematical, financial, statistics etc… functions, which will take as input and output parameter, excel tables.
This looks like a nice enhancement to excel formulas, which only support few build in functions with xls tables input, like vlookup, hlookup and which doesn’t support at all, functions with table output.
But, if i understood correctly, from your first demo version, there is no way to see which table has been created by which table function (functionality brought by xls formula bar, for xls cells) and it is not possible to refresh output table data manually or automatically, if one of its source tables, has been updated (for analogy; excel, does this automatically, for cells with formula).
I believe that these 2 features will be exteremely useful for usability of “excel functions with tables parameters” add-in.
My question is simple; Do you have any plan, about adding these features or some similar functionality, to productive version of your add-in?
thanks & regards
Here is a complete c# Excel tutorial…
http://csharp.net-informations.com/excel/csharp-excel-tutorial.htm
Lethal
New release B104 of excel add-in (Finaquant in Excel) is available for download.
Information about the 10 new table function added in this release:
https://plus.google.com/b/101460024578785928760/101460024578785928760/posts/H89tFCoZb1i
How can multiply two matrices using : FQ_matrix_multiplication. I would be grateful for example as for inverse function.
I’ve heard a lot of good about this website. Sadly, everything I try to download seems corrupted, even the pdfs. What about it?
Termites were farming 25 million years ago — long before humans (The Verge)
http://www.theverge.com/2016/6/25/12024324/termite-farming-25-million-years-ago-before-humans
I can not download any of the files above
I want these two files for learning
Basic Matrix and Vector Functions with VBA/Excel (October2014, V1.7)
BasicMatrixAndVectorFunctionsInVBA-V1_3.xlsm (September2012, V1.3)
My current interests (LinkedIn post)
https://www.linkedin.com/posts/tunc-ali-kutukcuoglu-10566445_ecology-education-permaculture-activity-7008747513138581504-eOcz
* ecology & economy, sustainable communities, settlements & lifestyles
* ecological gardens & aquariums (incl. permaculture)
* music composition & production (music technology; recording, mix, master)
* ecological enlightenment against ecological ignorance of mainstream economics (Church of Economism) and industrial paradigm
* multifunctional & multidisciplinary education (ecology, philosophy, gardening, music etc.)
* tennis training (assistant tennis trainer)
” renewable energies, especially solar energy (small scale)
My PhD: Why does mainstream economics ignore ecology? (see pdf download on page)
https://www.tuncalik.com/2021/02/my-phd-thesis-why-does-mainstream-economics-ignore-ecology/
My short story: Wild Drummer of the Apple Village
https://www.tuncalik.com/2022/12/an-anatolian-tale-wild-drummer-of-the-apple-village/
My book: Ecological Ignorance in Mainstream Economics (available at Amazon)
https://www.tuncalik.com/2021/11/my-book-ecological-ignorance-in-mainstream-economics/
My music: Wild Drummer is coming!
https://musescore.com/user/31102457/scores/9482026