Spreadsheet Fund Documentation

Getting Started


Overview


Spreadsheet•Fund is a Google Sheets Add-on that provide templated sheets and custom functions which together allow you to automate and simplify all operations in a typical investment fund with the "mutual fund" model. You can use the Add-on for tracking your protfolio, accounting investor's shares, recording and reporting fund operations and performance.

Mutual fund involves the release of some internal unit which represents all assets of the Fund. The price per share is simple:
total value of all assets in the fund / amount of shares


Deposits and Withdrawals should not affect on a price of the unit.
We need to mint new units on each deposit event and burn units on each withdrawal event. For these purposes, the Issuance tab is made and the mechanisms for issuing and burning coins are automated.


How to install the Add-on

Watch the video here: https://www.youtube.com/watch?v=K10sfTCsERo

1. Open the link https://chrome.google.com/webstore/detail/spreadsheet-fund/pkjmaijhdpkmfogpmfaennidncpdgmbl
2. Click on the "Install" button
3. New spreadsheet will be created on your Google Drive
4. Click on the "Add-ons" in the Toolbar > Spreadsheet Fund > Setup > Initialize Fund
5. Go through the "Help Wizard": enter general config, load templates


Templated sheets

Add-on provides a set of templated sheets which are specially prepared with formulas and formatting to organize fund administration automation in Google Spreadsheet.

Logic and mathematical basis is created using the experience of dozens of professional fund managers.
How to download the templates?

1. Add-Ons > Spreadheet Fund > Setup > Initialize Fund
2. Enter General config of your Fund and click on the "Load templates".
3. The Dialog will ask you to continue. Click "Yes" and wait for the templates to load.


To avoid errors - please keep the "templated" sheets with the strict naming and do not Insert columns between already existed columns.
How to setup balances

You can enter balances manually on the left section of the "Balances" sheet.
See the screenshot


To setup auto sync - enter you API keys and wallet's addresses on:

Add-ons > Spreadsheet Fund > Settings > Balances
How to init shares

Once all your balances are taken into account - next step is to issue mutual fund units (shares):

1. Add-ons > Spreadsheet Fund > Setup > Initialize shares
2. Enter initial price per PIE
3. Add all shareholders and their shares and click "Initialize".

You should see new rows on the "Issuance" tab. And John, Sam and Alice will appear on the "Investors" tab with their equivalent shares (units)

Fund operations

Update rates - updates all prices for all coins and stocks on the "tickers" sheet. It also updates the "O1" cell on the "Portfolio" sheet with random number to trigger cache updates.

It uses 4 requests for one update (don't matter how many assets in your portfolio). Use it carefully and rememeber about 20 000 requests per day limit per Google account.

ticker & =tickers custom functions
There are two custom fuctions for retreiving price from the "tickers" sheet.

You can get the price of any crypto/fiat/stock/custom asset in any other crypto/fiat/stock/custom asset. For example:

=ticker("BTC:BITCOIN";"TUSD")
=ticker("BTC:BITCOIN";"ETH:ETHEREUM")
=ticker("AAPL":"BTC:BITCOIN"), etc


tickers is more optimized function which get an array of symbols as input and return array of prices.

Sync balances

Click on this button - will run auto sync requests to pull new balances on the "Balances" sheet.

If you got some errors - firstly please check your API keys and Addresses in the Settings. If everything is correct - contact support at spreadsheetfund@gmail.com or leave a bug report
here
Record data

Click on this button - will record the snapshot of your portfolio on the "PortfolioHistory" sheet and adds new record on the "Performance" sheet.

If you already made records today - the script will update them with new data.

Publish data

Click on this button - will copy sheets from your private spreadsheet into the public spreadsheet.

You need to setup in Settings:
- Public Spreadsheet ID
- Sheet names (through commas)

Investor IN

This popup is used to account investor's deposits in your fund.

Enter the ID of investor who made a deposit, amount and currency. According to the current fund share (unit) price (data is taken from the "Portfolio" sheet) - the script will calculate how much units need to issue.

Click the "Enter" button and new row will be added on the "Issuance" tab.

If you enter "Rederral ID" - the script will calculare referral fee too.

Fees are recorded in the currency in which the deposit is made.

Proper order of actions:

1. Investor have already send money (for example directly on the main ETH wallet of your fund)
2. You first release tokens, and only then update the balances
3. Change status from "pending" to "done"


Investor OUT

This popup is used to account investor's withdrawals from your fund.

Enter the ID of investor who want to make a withdrawal. The script will calculate available balance of units and the size of payment in the desired currency.

Fees are recorded in the currency in which the withdrawal is made.

Click the "Redeem" button and new row will be added on the "Issuance" tab.

Proper order of actions:
1. Investor asks for withdrawal (amount and currency)
2. You calculate the amount of units and make a record on the Issuance tab
3. You do a transaction
4. Update balances

5. Change status from "pending" to "done"


Transer units

This popup is used to transfer units from one investor to another

It geneartes two records on the "Issuance" tab.

No fees here.

Performance Fee

A performance fee is a payment made to an investment manager for generating positive returns.

You need to enable "Performance Fee" in the Settings and enter the desired % of profits.

The proper model of taking "Performance fee" involves the following:
1. It's calculated individually for each investor. Because at the same time someone can have a profit, and someone is not.
2. Profits are calculated by the weighted average price of buying fund's share (unit) comparing to the current price.
3. After fee is taken for investor - next time it can be taken again ONLY if current price will be greater than weighted average price of buying
4. Weighted average price of buying becomes equal the current price at the moment of fee taken. All remaining units of the investor are considered purchased at this price.
5. Performance fee can be taken at any time (at the discretion of the Fund Manager and policy of your fund)
6. Performance fee must be taken at each withdrawal from the Fund

Profits are calculated by the base currency (specified in the Settings)

Example and proper order of actions:
1. You have investors with profits and you want to take your Performance Fee
2. Click on the "Performance Fee" button and look at numbers
3. Enter the desired currency for payment transaction. For example "TUSD"
4. Click on the "Take" button and new records will occur on the "Issuance" tab
5. Make a transaction from the Fund's wallets
6. Update balances
7. Change statuses from "pending" to "done"


Settings

Fund fees

To open click on the Add-ons > Spreadsheet Fund > Settings > Fund fees

Entry fee/Redeem fee is taken at the Deposits/Withdrawal in the currency of the deposit transaction.

It can be splited between 3 beneficiaries:
1. Fund managers (you have to specifu "Manager ID" on the General tab
2. Fund pool
3. Referral (entry fee only)

If no Referral - you can setup who (Managers or Fund) will take his share.


Reporting

Add-ons > Spreadsheet Fund > Settings > Reporting

Here we can setup the time for automatic recording and publishing data

Telegram notifications

Add-ons > Spreadsheet Fund > Settings > Telegram notifcations

1. Create a bot to get API token by this instruction
2. Write to your bot or add him into your group chat (with "write" permissions)
3. To get your chatID write to the bot @myidbot and write /getid command
3.1 To get groupchatID add @myidbot to your group and write /getgroupid command
3.2 You also can use @channelname as Chat ID parameter

Portfolio tracking notifications - send message with portfolio statistics (total values, unit prices, % change)
Error alerts about recording to the Performance & PortfolioHistory sheets - send messages with errors like zero prices or something else