Getting Started
Overview
Spreadsheet•Fund - is a Google Sheets Add-on that provides 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 portfolio, accounting for investor's shares, recording and reporting fund operations and performance.
A mutual fund involves the release of some internal unit that represents all assets of the Fund.
Price per Unit = Total Value of all assets / amount of units
Deposits and Withdrawals should not affect the price of the unit.
We 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.
Overview
Spreadsheet•Fund - is a Google Sheets Add-on that provides 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 portfolio, accounting for investor's shares, recording and reporting fund operations and performance.
A mutual fund involves the release of some internal unit that represents all assets of the Fund.
Price per Unit = Total Value of all assets / amount of units
Deposits and Withdrawals should not affect the price of the unit.
We 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
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
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 that are specially prepared with formulas and formatting to organize fund administration automation in Google Spreadsheet.
Logic and mathematical basis are created using the experience of professional fund managers.
Add-on provides a set of templated sheets that are specially prepared with formulas and formatting to organize fund administration automation in Google Spreadsheet.
Logic and mathematical basis are created using the experience of professional fund managers.
How to download the templates?
1. Add-Ons > Spreadsheet Fund > Setup > Initialize Fund
2. Enter the 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.
1. Add-Ons > Spreadsheet Fund > Setup > Initialize Fund
2. Enter the 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
You can enter balances manually on the left section of the "Balances" sheet.
See the screenshot
To set up auto-sync - enter your API keys and wallet's addresses on:
Add-ons > Spreadsheet Fund > Settings > Balances > Add
Add-ons > Spreadsheet Fund > Settings > Balances > Add
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 shares"
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)
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 shares"
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 Market 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.
Update Market 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
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 Performance
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.
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 Sheets
Click on this button - will copy sheets with all values (not formulas) from your private spreadsheet into the public spreadsheet.
You need to set up in Settings:
- Public Spreadsheet ID
- Sheet names which should be copied (through commas)
Click on this button - will copy sheets with all values (not formulas) from your private spreadsheet into the public spreadsheet.
You need to set up in Settings:
- Public Spreadsheet ID
- Sheet names which should be copied (through commas)
Investor IN
This popup is used to account for investor's deposits in your fund.
Enter the ID of the 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 many units will be issued.
Click the "Enter" button and a new row will be added on the "Issuance" tab.
If you enter "Referral ID" - the script will calculate the referral fee too.
Fees are recorded in the currency in which the deposit is made.
Proper order of actions:
1. Investor has already sent money (for example directly on the main ETH wallet of your fund)
2. You release tokens through the "Investor IN" popup, and then update your balances
3. Change status of the tx on Issuance tab, from "pending" to "done"
This popup is used to account for investor's deposits in your fund.
Enter the ID of the 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 many units will be issued.
Click the "Enter" button and a new row will be added on the "Issuance" tab.
If you enter "Referral ID" - the script will calculate the referral fee too.
Fees are recorded in the currency in which the deposit is made.
Proper order of actions:
1. Investor has already sent money (for example directly on the main ETH wallet of your fund)
2. You release tokens through the "Investor IN" popup, and then update your balances
3. Change status of the tx on Issuance tab, from "pending" to "done"
Investor OUT
This popup is used to account for investor's withdrawals from your fund.
Enter the ID of the investor who wants to make a withdrawal. The script will calculate the 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 a new row will be added on the "Issuance" tab.
Proper order of actions:
1. Investor asks for withdrawal (1 ETH)
2. You calculate the number of shares and make a record on the Issuance tab through the "Investor OUT" popup.
3. Make a transaction with 1 ETH to the investor
4. Update balances
5. Change status from "pending" to "done"
This popup is used to account for investor's withdrawals from your fund.
Enter the ID of the investor who wants to make a withdrawal. The script will calculate the 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 a new row will be added on the "Issuance" tab.
Proper order of actions:
1. Investor asks for withdrawal (1 ETH)
2. You calculate the number of shares and make a record on the Issuance tab through the "Investor OUT" popup.
3. Make a transaction with 1 ETH to the investor
4. Update balances
5. Change status from "pending" to "done"
Transfer Shares
This popup is used to transfer units from one investor to another
It geneartes two records on the "Issuance" tab.
This popup is used to transfer units from one investor to another
It geneartes two records on the "Issuance" tab.
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 the fee is taken for investor - next time it can be taken again ONLY if the current price will be greater than the weighted average price of buying
4. Weighted average price of buying becomes equal to 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 the numbers
3. Enter the desired currency for a payment transactions. For example "ETH"
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"
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 the fee is taken for investor - next time it can be taken again ONLY if the current price will be greater than the weighted average price of buying
4. Weighted average price of buying becomes equal to 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 the numbers
3. Enter the desired currency for a payment transactions. For example "ETH"
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 split between 3 beneficiaries:
1. Fund managers (you have to specify "Manager ID")
2. Fund pool
3. Referral (entry fee only)
If no Referral - you can setup who (Managers or Fund) will take his share.
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 split between 3 beneficiaries:
1. Fund managers (you have to specify "Manager ID")
2. Fund pool
3. Referral (entry fee only)
If no Referral - you can setup who (Managers or Fund) will take his share.
Recording Performance
It creates records on PortfolioHistory and Performance tabs automatically every day.
To set up - just select the time you want to make a record.
To sync balances before each record - select the checkbox.
Market rates will be updated automatically in time of the record.
It creates records on PortfolioHistory and Performance tabs automatically every day.
To set up - just select the time you want to make a record.
To sync balances before each record - select the checkbox.
Market rates will be updated automatically in time of the record.
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
3. To get your chatID to write to the bot @myidbot and write /getid command
3.1 To get groupchatID to add @myidbot to your group and write /getgroupid command
3.2 You also can use @channelname as Chat ID parameter
Portfolio tracking notifications - the bot will send a message with portfolio statistics (total values, unit prices, % change)
Fund operations - the bot will send notifications about deposits, redeems, performance fees
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
3. To get your chatID to write to the bot @myidbot and write /getid command
3.1 To get groupchatID to add @myidbot to your group and write /getgroupid command
3.2 You also can use @channelname as Chat ID parameter
Portfolio tracking notifications - the bot will send a message with portfolio statistics (total values, unit prices, % change)
Fund operations - the bot will send notifications about deposits, redeems, performance fees