Perp Finance CoinGecko API

# Perp Finance CoinGecko API ## API endpoints ### /contracts The /contracts endpoint provides a summary of all contracts traded on the exchange. Response: ``` [ { "ticker_id": "ETH_USDC", "base_currency": "ETH", "target_currency": "USDC", "last_price":"1400", // last price of ETH in USDC "base_volume":"637", // 24 hr volume in ETH "target_volume":"892538", // 24 hr volume in USDC "high":”51.3”, // highest price in last 24hrs “low”:”49.2”, // lowest price in last 24hrs "product_type": "Perpetual", "open_interest": , "funding_rate": , "next_funding_rate_timestamp": , // assumption that it's 1 hr in perp }, ...similar objects for YFI, BTC, DOT and more ] ``` ### Response object params - **ticker_id, base_currency, target_currency, funding_rate**: A ticker like ETH-USDC, BTC-USDC etc. We need to fetch all available markets on Perp to show these info. We'll do that by calling redash API for funding rates as it shows funding rates of all the asset pairs alons with asset pairs name as well. Funding rate can also be fetched from subgraph's funding rate changed event. ```graphql { fundingRateUpdatedEvents(first: 5, orderBy: blockNumber, orderDirection: desc, , where: { amm_in: ["0x8d22F1a9dCe724D8c1B4c688D75f17A2fE2D32df"]}) { id amm rate underlyingPrice timestamp } } ``` - **base_volume, target_volume**: From redash we can get the volume for past 24 hours in terms of USDC (**target_volume**) To get volume in eth we need to calculate it over the time unless we do `base_volume = target_volume / priceETH` which would be very wrong due to ETH price volatility during those 24hrs. **We suggest to add eth volume key stats over time into the redash dashboard** ADD postionalETH sum in the following query: ```sql SELECT SUM(fee) as _fee, marketPair, SUM(positionNotional) as _totalNotionalTraded, CAST(DATE(logTimestamp) as string) as _date FROM perp."perp-server-production-TraderPositionLogTable" WHERE eventType = 'PositionChanged' AND DATE_DIFF('DAY', DATE(logTimestamp), CURRENT_DATE()) < {{ WITHIN_DAYS }} GROUP BY DATE(logTimestamp), marketPair ORDER BY DATE(logTimestamp) DESC ``` - **high, low** For high low we will scan subgraph over past 24 hours and get the lowest/higest spot prices given on position changed event. - **last_price** entry price of last tx(position changed event) => current rate of eth in usdc  ```graphql { positionChangedEvents(first: 1, orderBy: blockNumber, orderDirection: desc, where: { amm_in: ["0x8d22F1a9dCe724D8c1B4c688D75f17A2fE2D32df"]}) { id spotPrice fundingPayment } } ``` - **open_interest** What is open interest -> using long / short open interest, should we add them for total open interest? (mostly yess we add them) Open interest over last 24 hours? Is that what redash shows us? probably not. It shows us the current open interest across all positions. Coingecko expects open_interest over last 24 hrs, how do we return that? ### Question and Doubts - What to return for open interest? Redash returns us current open interest for long and short positions of every market. Should we just add them and return? - How do we get base volume for last 24hrs? (Volume in ETH, DOT etc) Base Volume and Target Volume can be seen in redash on `USDC Trading Volume by Day`, they should be fetched from **Trades By Market API but that is not working atm** - Are there any other fields which we'd like to return or any other API we'd like to create. Like we could show fees generated over last 24hrs etc. ### Research for Data Sources Coingecko Methodology Page: https://www.coingecko.com/en/methodology  - Data sources are queried using entities called `queries` - `Queries` are combined into `dashboards`. - For us Dashboard Slug == API KEY to use - Slug = rpCOTkyNbXOUsanfyi4HvcT5wgougGGhCMPa7GKS - GET Request to the public dashboard to dump json to file ```bash curl 'https://app.redash.io/perp/api/dashboards/public/rpCOTkyNbXOUsanfyi4HvcT5wgougGGhCMPa7GKS' -H 'Authorization: Key rpCOTkyNbXOUsanfyi4HvcT5wgougGGhCMPa7GKS' > data.json ``` Find all queries' names and ids ```bash cat data.json | jq '.widgets[].visualization.query | [.id,.name]' ``` How to get `results` for a `query` ? Step 1: Get `query` api key for each `query` id we got from above we will send a GET request to the the below endpoint and get its metadata. Note that the metadata contains good info about the query like when was the last time it was refreshed along with who created it. EXAMPLE ```bash= curl 'https://app.redash.io/perp/api/queries/587893' -H 'Authorization: Key 9IZxDZkgbpcq6OWgy2B9lt8SCNMesxaZU8JU3kvX' | jq { "id": 587893, "latest_query_data_id": 873722424, "name": "7day Key Stats", "query": "SELECT\n SUM(fee) as _fee,\n COUNT(*) as _numberOfTransactions,\n count(DISTINCT(traderAddr)) as _uniqueTraders,\n SUM(positionNotional) as _totalNotionalTraded,\n SUM(positionNotional) / count(DISTINCT(traderAddr)) as _averageTradeVolumePerWallet,\n SUM(positionNotional) / COUNT(*) as _averageTransactionValue,\nFROM\n perp.\"perp-server-production-TraderPositionLogTable\"\nWHERE\n eventType = 'PositionChanged'\n AND DATETIME(logTimestamp) > CURRENT_DATETIME() - INTERVAL 7 DAY", "query_hash": "e9e1695370c999ded9406134d2f1b384", "api_key": "9IZxDZkgbpcq6OWgy2B9lt8SCNMesxaZU8JU3kvX", "updated_at": "2020-12-30T16:13:23.270Z", "created_at": "2020-12-28T09:16:04.504Z", "data_source_id": 39845, "version": 1, "tags": [ "Production" ], "is_safe": true, "user": { "id": 341086, "name": "Yenwen Feng", "email": "yenwen@perp.fi", . . ... ``` We now use this new api key to get results from the query We can get the raw query and the results in the rows ```bash curl -sS 'https://app.redash.io/perp/api/queries/587893/results' -H 'Authorization: Key 9IZxDZkgbpcq6OWgy2B9lt8SCNMesxaZU8JU3kvX' | jq '.query_result | [.query, .data.rows[]' [ "SELECT\n SUM(fee) as _fee,\n COUNT(*) as _numberOfTransactions,\n count(DISTINCT(traderAddr)) as _uniqueTraders,\n SUM(positionNotional) as _totalNotionalTraded,\n SUM(positionNotional) / count(DISTINCT(traderAddr)) as _averageTradeVolumePerWallet,\n SUM(positionNotional) / COUNT(*) as _averageTransactionValue,\nFROM\n perp.\"perp-server-production-TraderPositionLogTable\"\nWHERE\n eventType = 'PositionChanged'\n AND DATETIME(logTimestamp) > CURRENT_DATETIME() - INTERVAL 7 DAY", { "_fee": 224011.90626407633, "_numberOfTransactions": 61780, "_uniqueTraders": 163, "_totalNotionalTraded": 224206149.7405449, "_averageTradeVolumePerWallet": 1375497.851168987, "_averageTransactionValue": 3629.1056934371136 } ] ``` This and other queries should be useful for us to build the coingecko api ## Queries provided by redash at the moment ### Cumulative Trades by market ``` ID: 591200 API_KEY: 0fFwyKMin6SuKvwFNnkl5GWrHaV4K7dJJohIE3Ab ``` Gives cumulative fees(fees) and cumulative notional(volume) of everyday since inception ### Trades by market ``` ID: 572044 API_KEY: OyWAdclC48jK2zVJGJaVEDHC0MI2ngb423DWGYmO ``` "No cached result found for this query." Ideally should provide us USDC trading volume and fees per day in different markets. ### Open Interest ``` ID: 572602 API_KEY: WBbCksIpTx3y51XiJzCIQRjZ32kHHyM7mdcmeO4R ``` Open interest in USDC and base asset for both long and short positions ### Funding Rate ``` ID: 572055 API_KEY: PkTaxunGDutppsVcjuwtqje78PjRrfV70R49hco4 ``` Tells current funding rate and past funding rates (one hour gap) for all the pairs ### 24hr Key Stats ``` ID: 590378 API_KEY: H1PNIDwc9UWCcAB7qUg86rqq4KMaoy4eSIDd0Tfd Response: { "_fee": 37282.569159553364, "_numberOfTransactions": 10044, "_uniqueTraders": 108, "_totalNotionalTraded": 37352385.10827206, "_averageTradeVolumePerWallet": 345855.4176691857, "_averageTransactionValue": 3718.875458808449 } ``` ### Total Key Stats ``` ID: 575812 API_KEY: HX3g7QY9oNmxTxfn6vZWaNzrlhVEMq8l92DlmPcP Response: { "_fee": 668556.8663450133, "_numberOfTransactions": 228752, "_uniqueTraders": 311, "_totalNotionalTraded": 669161007.512909, "_averageTradeVolumePerWallet": 2151643.110973984, "_averageTransactionValue": 2925.2684457967976 } ``` ### 7day Key Stats ``` ID: 587893 API_KEY: 9IZxDZkgbpcq6OWgy2B9lt8SCNMesxaZU8JU3kvX Response: { "_fee": 221536.03963699687, "_numberOfTransactions": 61394, "_uniqueTraders": 165, "_totalNotionalTraded": 221714487.86258957, "_averageTradeVolumePerWallet": 1343724.1688641792, "_averageTransactionValue": 3611.3380438249596 } ``` ### Trades by Market ``` { "message": "No cached result found for this query." } ```