ethanopp / gsheets-3c-portfolio-manager Goto Github PK

Since this is versioning so fast and is a bit annoying to keep generating API keys / deleting sheets we need a function to pull this and delete the sheet.

This would use the document / script properties to find the "last risk manager sheet" and delete it.
Then using user properties we can pull the api keys and populate into the new script.
Need to see if we can make sure the old triggers are cleared as well.

Pushing more than 5000 rows locks up the Google sheet entirely. Working on building a way to push the data in 1000 row chunks after it's pulled from the API, or as the data comes in.

Keep in mind the 5M limit.

Right now the handler limits at 5k deals. 3Commas returns oldest deals first. This means if you have 10k deals to look at, the dashboards focus on most recent deals but you only get to see the oldest deals.

We essentially need to query all deals and drop the first set up to 5k or find a way to get 3C to query on dates only.

It would be relatively easy to create a bot manager sheet that enables users to modify settings of their bots and push to 3Commas. This would need to be a SEPARATE api key that only has bot access, and something that needs to be toggled on or copied completely independently. Just to keep people trusting the sheet. All the code base is already there to do this.

Time commitment: 1-2 hour project

Idea to enable:

1. Dropdown in the menu or a toggle on the sidebar to turn on
2. API key popup to input a key for JUST THE bot manager
3. click setup. This adds the bot manager tab to the sheet.
4. Then boom. profit.

Shows metrics related to total overall performance.

Data tables that need to be built

Daily Performance

Showing the performance of your portfolio daily

We may be able to make this into a formula that accepts days back from today as a date filter sort of. The query would still be required to fetch ALL deals from the API and then make calculations based on this.

Columns:

• Date
• Total Profit
  • Calculated from the deals table
• ROI
  • Unsure how to calculate this without past days close numbers. This could be based on a total invested number. Total invested + daily profits, running down. But that would require adding deposits and when they happened.
• Number of bots running that day
  • This may end up just being unique bots with active deals, but that could be skewed if the bot has a deal running longer than a day. If this works we can also add profit by bot by day. We could get creative here to calculate this. Possibly using the open date vs close date. If the deal is still opened, and didn't close it's an open bot for the day.
• Running profit
  • Just the sum of the profit running from day 0. Couldn't filter based on this easily.
• Total invested + profit
  • If this is able to be calculated using a deposit invested scheduled maybe we can calculate a rough daily ROI. The only issue here is that if you're taking funds in crypto this ROI can be a very dynamic number.

Rows

• Just date

Profit by Hour

Easy to calculate. Each deal's close date just pull the hour and give profit by hour converted to local timezone. Not an essential chart but fun to look at to see when the most volatile times are.

Pair performance by day

Calculating how good each pair does based on the calendar day. This could be fed into a line chart showing total profit by pair by day.

Columns

• Pair
• Pair total profit
• Pair deals closed
• Maybe average minute / hours per deal that day

Charts to be built

• Profit by hour
• Profit by day
• Profit by month
• Total profit accumulating.
• Bot daily performance
• Pair daily performance

Nice to Haves

• Ability to customize an expected ROI
  • This would then calculate your expected ROI and we can chart based on expected ROI vs actual ROI.

When switching from paper to real it goes from USDT (paper) to USD (real) but the page is still set to USDT. Need to see if we can force this to go back to a valid value.

https://discord.com/channels/720875074806349874/835100061583015947/851237071389589525

Build summary accounts table with reserved field customization.

We need the ability to select ALL currency and have this update across the sheet. Right now it's doing this based on a selection change

// Store sheet name in instructions H1 for query reference
function onSelectionChange(e) {
    const prop = PropertiesService.getScriptProperties();
    const previousSheet = prop.getProperty("previousSheet");
    const sheetName = e.range.getSheet().getSheetName();
    if (sheetName != previousSheet) {
        SpreadsheetApp.getActive().getRange("Instructions!H1").setValue(sheetName);
    }
    prop.setProperty("previousSheet", sheetName);
}

Proposed Solution

onEdit

Add to OnEdit. When you edit a sheet with the denomination or account, it pulls that data off the sheet and stores it in a script property.

Script Functions

We create a account_id, currency, and AccountName custom function for GAS. These can populate data in the instructions sheet

Tasks

• - Update the onEdit to pull the currency based on the editing of those sheets.
• - Store this in a sheet value
• - Create the custom functions to pull the value
• - Update the formulas to use this.

Build out integrating grid bots into the API calls and a sheet tab. This will be used to calculate risk and on the summary analysis tabs.

• API docs - https://discord.com/channels/@me/850083513737805884/851200299649728563
• Need to calculate total money possible (risk). It will most likely be Total grids * amount per grid

API call example

{
id: 5                                     
account_id: 10                            
account_name: 'My account'                
is_enabled: true                          
grids_quantity: '20'                      
created_at: 2018-08-08 08:08:08           
updated_at: 2018-08-10 10:10:10           
lower_price: '8000'                       
upper_price: '10000'                      
quantity_per_grid: '100'                  
leverage_type: 'custom'                   
leverage_custom_value: '20.1'             
name: 'GridBot1'                          
pair: 'BTC_ETH'                           
start_price: '9000'                       
grid_price_step: '100'                    
current_profit: 100                       
current_profit_usd: 1000                  
bought_volume: 1000                       
sold_volume: 1000                         
profit_percentage: 0.1                    
current_price: 100.1                      
investment_base_currency: 100             
investment_quote_currency: 100            
grid_lines: GridLineEntity    
} 

This would require adding step % from the API on the deals table, then creating a calculated metric for max drawdown % and handling once it hits 100%.

It might be best to move the whole Drawdown (query) table to the backend to be calculated as an active deals table to merge tables together.

• - Add an automation toggle on the sidebar
• - Info tab on the sidebar. (Last sync, total accounts, valid API keys, etc)