admin


Statistical Sheets

Built-in pre-formatted ready-to-use templates. The Statistical Sheets (SS) are a set of the 3 reports that can be produced on the fly in less than 10 seconds (and depending how fast is you access to your database).
SS1: This is an executive summary of a fund’s track record. It shows the performance of the selected fund along with 4 optional benchmarks. Key metrics are grouped in 3 different time windows: Total period, last 3 years and recent statistics where the last 12 months are shown.

This is followed by a small correlation matrix follows with a VAMI chart. VAMI stands for value added monthly indicator, that is the cumulative value an investor gets when invests in the fund.

In the second page includes a performance table, showing monthly performance numbers and YTD totals, together with a set of four charts: Rolling monthly correlation against the benchmarks, autocorrelation for different time windows to see seasonality and Level 3 assets, standard deviation and underwater curve.

 

 

 

 

 

 

SS2: This report is an extension of the the SS1. It shows differs views of the performance track record of the fund against 2 selected benchmarks. the first page shows a more deepen view of the underwater curve, monthly returns over time, and a 12 months rolling standard deviation chart.

The second page shows 12 months rolling returns, monthly returns bars, 12 months rolling Sharpe ratio, 12 months rolling differential returns (against the benchmarks), monthly differential returns against each of the benchmarks, a frequency distribution chart and a gain vs

The last page shows a scatter plot with a regression line for the fund against each of the selected benchmarks in which is easy to visualise the correlation between time series. A fitting equation, together with a squared-R results are also included.

Below includes charts depicting 12 months rolling betas and correlations to the selected benchmarks.

 

 

 


Fund management tools for Excel

 

qUtopic for Excel is a fully integrated Excel tool aimed at teams managing portfolios of funds or hedge funds. The tool helps analysts, portfolio managers and risk managers to visualise fund data quickly and effectively. It also helps marketing professionals to produce customised homogeneous top quality fact sheets and simulated portfolios.

qUtopic for Excel is easy to install and easy to use. It’s a set of Add-ins that are easy to deploy in stand alone installations or with in corporate networks.

qUtopic for Excel is fast because it has been developed in C++.  The more than 100 functions in its library are integrated with the rest of native functions and fully documented. Functions return both, singles values and arrays.

qUtopic comes with a set of factsheets and other templates that are easy to customise.

qUtopic also provides a quantitative ranking for short list analysis.

 


Use Excel to create a Balance Sheet view

A balance sheet view in Excel

 

The basic accounting equation, also called the balance sheet equation, represents the relationship between the assetsliabilities, and owner’s equity of a business. It is the foundation for the double-entry bookkeeping system. For each transaction, the total debits equal the total credits. It can be expressed as

 \text{Assets} = \text{Liabilities} + \text{Capital}  [1]
 a = l + c

In a corporation, capital represents the stockholders’ equity. Since every business transaction affects at least two of a company’s accounts, the accounting equation will always be “in balance,” meaning the left side should always equal the right side. Thus, the accounting formula essentially shows that what the firm owns (its assets) is purchased by either what it owes (its liabilities) or by what its owners invest (its shareholders equity or capital).

(from Wikipedia)

And because we are plotting a corporate balance sheet, we state the formula:

Assets = Liabilities + Equity

, which in turn could be expressed as

E=A-L

This basic and simple view of the balance sheet (BS) allows managers to grasp the full picture very quickly.

As a way of example, we have translated into Excel the BS of an imaginary legal entity that holds a portfolio of funds. This entity is being financed by investors in the equity tranche in 3 different currencies: US dollars, Swiss Francs and Euros. It also uses a credit line to boost its performance. On the assets side, besides the portfolio, there is unencumbered cash, cash reserved solely to cure any margin call, and a margin deposited in a bank as a collateral of the FX hedging needed.

In a blink, we can asses how far away the entity is from being distressed. We can also elaborate a visual understanding of how much the portfolio is actually levered. And we can asses what parts of the equity tranche (net asset value or NAV) are being funded in each currency.

The invested portfolio is the blue area that is further subdivided into lighter blues indicating funds grouped by different liquidity. Assets are presented sorted by liquidity, therefore the first lighter blue area indicates funds with weekly and monthly liquidity, the second with quarterly and the rest are less liquid funds.

We have done all this using a set of Store Procedures and qUtopic functions connected to a Webfolio’s database running on SQL Server .


Connecting qUtopic templates to Azure

All templates’ sample data are stored in a database in Azure so you can quickly check the functionality of the Excel Add-In.

If you are behind a Firewall you need to make sure that your port 1433 is not blocked.
note Note

In addition to configuring the Azure SQL Database firewall, you may also need to configure the firewall on your network and local computer. To access a Azure SQL Database database from your computer, ensure that the firewall on your network and local computer allows outgoing TCP communication on TCP port 1433. (The Microsoft Azure SQL Database service is only available with TCP port 1433.)

Further reading can be found here: http://msdn.microsoft.com/library/azure/ee621782.aspx


Rapid Application Development with Excel and databases

Rapid Application Development (RAD), Agile Development, and Scrum, have been major topics in the front offices of large and medium size companies worldwide. The urgent need of having an application working ASAP have put Excel as the main tool at hand.  Excel facilitates the work because it is a flexible and familiar user interface. Excel can perform calculations, format text, and display charts natively. However, the connection to databases have been an obscure part of it. Mostly solved via a Data Connection Object, which is complicated and difficult to share with colleagues or using a VBA macro, that despite being effective, not always perform as fast and robustly as desired.

At qUtopic we know these issues because we have been dealing with them for many years. Our answer are the qUtopic functions. A set of database querying functions encapsulated in an XLL Add-in coded in C++.

qUtopic is an ideal lightweight affordable solution to any RAD development. Try qUtopic today!

 

 


Connect using an ODBC string

ODBC

In computingODBC (Open Database Connectivity) is a standard programming language middleware API for accessing database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems; an application written using ODBC can be ported to other platforms, both on the client and server side, with few changes to the data access code.

ODBC accomplishes DBMS independence by using an ODBC driver as a translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS. An ODBC driver can be thought of as analogous to a printer or other driver, providing a standard set of functions for the application to use, and implementing DBMS-specific functionality. An application that can use ODBC is referred to as “ODBC-compliant”. Any ODBC-compliant application can access any DBMS for which a driver is installed. Drivers exist for all major DBMSs, many other data sources like address book systems and Microsoft Excel, and even for text or CSV files.

ODBC was originally developed by Microsoft during the early 1990s, and became the basis for the Call Level Interface (CLI) standardized by SQL Access Group in the Unix and mainframe world. ODBC retained a number of features that were removed as part of the CLI effort. Full ODBC was later ported back to those platforms, and became a de facto standard considerably better known than CLI. The CLI remains similar to ODBC, and applications can be ported from one platform to the other with few changes.

Most commonly used ODBC connection strings
We suggest using the following string when connecting to a local database (for example SQL Express or MS Access running in the final user’s machine):

When connection to a networked SQL server within company:

DRIVER={SQL Server};SERVER=localhost\SQLEXPRESS;DATABASE=qUtopic;Trusted_Connection=Yes
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Data\AdventureWorks.accdb

When connecting to Azure SQL Server:

Driver={SQL Server Native Client 10.0};Server=tcp:vyi1qe1zgy.database.windows.net,1433;Database=qUtopicTest;Uid=TestUser@vyi1qe1zgy;Pwd=qUtopic1234;Encrypt=yes; 

When connected to a web hosted MySQL database:

Driver={MySQL ODBC 5.2 ANSI Driver};Server=192.xxx.xxx.xxx;Port=3306;Database=qUtopicTest;User=YourUserName;Password=YourPassword;Option=3;

 * Driver need to be downloaded from here.

 You can also find a variety of ODBC strings at this great useful website www.ConnectionStrings.com.

 

 

 


Simple functions

Paste_FunctionUse the power of Excel functions to build rich and complex reports in minutes.

qUtopic delivers a set of functions that enable power users to extract pieces of data right to the places where are needed. No more intermediary Query Tables, expensive VLOOKUPS, and extra Sheets.

Every function is fully documented and expanded with constant updates online.

 


Fast database querying

Speed

Runs up to 20 times faster than conventional VBA code. qUtopic.xll™ has been coded in fully portable C++, with no dependencies on .NET, that makes it very light and extremely fast.

Excel interprets VBA code in real time every time it runs. An XLL is an add-in precompiled in a language with a very modest overhead. Only the libraries used are included. However, qUtopic is the ideal companion to increase speed and portability of your VBA project.

An XLL is not macro but a compiled add-in, so once after installation, it doesn’t require the user to change settings or give approval every time is used.

XLL technology has been available for decades with a prominent use in trading floors with strong quantitative requirements.

 


Flexibly connect to databases locally or in the cloud

Flexible

 

With qUtopic.xll you can connect your spreadsheet to any database via ODBC using functions. That includes SQL Azure™, which is SQL in the cloud, Oracle®, MySQL™, MS Access®, and website data in many formats. We are continuously developing our product to easily include all popular sources.

You can request data from multiple data sources in a single spreadsheet as long you know your connections string and have the adequate clearance. No need to create a connection file.


Fully documented

HelpEach function is fully documented and seamlessly integrated with Excel. Just hit F1 and you will be brought the requested topic.

Additionally, qUtopic hosts an online forum where matters are discussed more in detail and new examples and templates are posted constantly.


Monthly report template “Blue”

Template_Blue

If you are a fund management company in need to communicate your investment products’ periodic performance your most cost-effective answer is Excel®. You can produce a high-quality eye-catching report with a professional look for your clients and prospects.

The report format should be stable enough to convey a branding message, but with certain flexibility to be a real useful communication tool.

You would probably like to add your comments, news, awards, and even cutting dates for new subscriptions, but most of the things will remain constant. Some data is repeated exactly every period. We call that static data. And some other data changes as time passes by. We call this dynamic data.

The great part of this Excel template is that uses “dynamic data” from an SQL Server database.  In this case, we have posted data in the cloud so you can test the report.

If you open this report, with qUtopic.xll installed, you will see only one sheet in your workbook. On the left how this report will look like in the classic WISWIG way and on the right all the data used.

We used blue to mark static input data, green locally calculated data, and orange data obtained from a database with qUtopic functions.

Take a look to the template! It’s free.


Cross-correlation template

CrossCorrelation01

Up to 250 x 250 instruments in a single correlation matrix. Each cell takes the value of the correlation between the instrument in the row and in the column displaying a numbers between -1 and 1. Then the matrix is coloured using red for 1 or high correlation, yellow for 0 or no-correlation, and green for -1 or uncorrelation.

 


Try Adventure Works with Azure

AdventureWorksAzure

In this example you can test how fast Excel could be dealing with 20,000 contact names coming from Azure. The spreadsheet uses a list of names obtained using the statement SELECT FirstName, LastName AS Name FROM Person.Person ORDER BY FirstName directly from then AdventureWorks2012 database.

Then using a ComboBox, which is a standard ActiveX control, you can choose one of those names to, in turn, feed another formula that gets details of that contact.


Technical specifications


Version of MS Excel® Excel 2007 (v12.0)  and newer
MS Excel® architecture 32-bit only
Windows® Windows Vista®
Windows XP®
Windows 7®
Windows 8®
Windows architecture 32-bit / 64-bit*
Disk space  Less than 1Mb
Connectivity ODBC
Web Services
Through ODBC MS SQL®, Oracle®, MS Access®
Data parsing String separator for columns and rows
JSON **
XML***
Execute views Yes
Execute Store Procedures Yes
Take Parameters Yes
Cache in memory Yes
Connection pooling Yes
* Requires admin rights to run installation.
** Limited to one level, no nesting.
*** Only lines to rows.

Functions to connect Excel with databases

excel2013-logo

qUtopic is a platform designed to help analysts and portfolio managers invest into hedge-funds and other actively managed funds. qUtopic is also a set of Excel functions designed to seamlessly include information from a database into your spreadsheet. Just leverage your expertise in using formulas to quickly grab the pieces of data you need. There is no need to use code or macros behind your spreadsheets. You only need to know your connection string and your query statement.

Create fund reports and connect them live to your data sources using just functions using qUtopic.

qUtopic is available as an Add-in for Microsoft Excel.

 

Follow us!