DEX Prices from Uniswap, Pancakeswap, Sushiswap, 1Inch in Google Sheets

Published in

Coinmonks

7 min readApr 2, 2021

UPDATE 17th May: Now you can call array of tickers instead of single cells UPDATE 26th April: LIST OF ALL AVAILABLE COINS HERE

For anyone looking to extract automatically cryptocurrency prices from Decentralized Exchanges.

No coding skills are required, made for non developers.

First step is to access the following template sheet to see how the data is retrieved in Google Sheets.

TEST GOOGLE SHEET EXAMPLE HERE

https://docs.google.com/spreadsheets/d/1QODede4loYFnd9ig_f4vRiO4J4uptxn8zIx3qRsLDeA/edit?usp=sharing

Second step, I’ll describe hot to get prices in Google Sheet.

Cryptocurrency DEX Prices Formula

If you are looking to retrieve the latest price for any cryptocurrency on Uniswap, Sushiswap, Pancakeswap or even 1INCH, you will need to use the CRYPTODEXPRICE() formula.

Syntax: CRYPTODEXPRICE(Token1, Token2, Exchange,[optional refresh attribute])

1st parameter: 1st ticker or its contract address example MKR or 0x9f8f72aa9304c8b593d555f12ef6589cc3a579a2 2nd parameter: 2nd ticker or its contract address 3rd parameter: ticker of dex exchange on which you are looking for rate 4th parameter: an optional fixed cell for automatic refresh of the data, this parameter

https://raw.githubusercontent.com/Eloise1988/CRYPTOBALANCE/master/CRYPTOTOOLS_V2.gs

The formula will import cryptocurrency prices into Google spreadsheets. By default, data gets transformed into a range of numbers so it looks more like a normal price data import.

PANCAKESWAP

PancakeSwap is a Binance Smart Chain-based DEX launched by anonymous devs, it’s similar to Ethereum’s SushiSwap in terms of community governance and the ability to farm liquidity provider tokens.

PancakeSwap uses an automated market maker (AMM) model. That means that while you can trade digital assets on the platform, there isn’t an order book where you’re matched with someone else. Instead, you trade against a liquidity pool.

Here’s how to retrieve the WBNB-LTC pair price from Pancakeswap:

Calling an array of DEX prices on Pancakeswap V1

Calling an array of DEX prices on Pancakeswap V2

UNISWAP

Uniswap is a decentralized finance protocol that is used to exchange cryptocurrencies. Uniswap is also the name of the company that initially built the Uniswap protocol. The protocol facilitates automated transactions between cryptocurrency tokens on the Ethereum blockchain through the use of smart contracts.

As of October 2020, Uniswap was estimated to be the largest decentralized exchange and the fourth-largest cryptocurrency exchange overall by daily trading volume. In March 2021, Uniswap was generating fees of approximately US$2–3 million daily for the liquidity providers who facilitate liquid markets for the cryptocurrencies being exchanged.

Here’s how to retrieve the WETH-UNI pair price from UniswapV2:

=CRYPTODEXPRICE(“WETH”,”UNI”,”UNI”)

Calling an array of DEX prices on Uniswap

1INCH DEX PRICES

The price feed comes from 1inch’s exchange on the ethereum network.

=CRYPTODEXPRICE(“UNI”,”USDT”,”1INCH”)

MATIC/POLYGON DEX PRICES

You can get the prices of matic tokens. The price feed comes from Sushiswap’s exchange on the polygon/matic network.

=CRYPTODEXPRICE(“WMATIC”,”WETH”,”MATIC”)

FANTOM DEX PRICES

You can get the prices of fantom’s smart chain tokens. The price feed comes from Sushiswap’s exchange on the Fantom network.

=CRYPTODEXPRICE(“WFTM”,”USDC”,”FTM”)

AVALANCHE DEX PRICES

You can get the prices of avalanche tokens. The price feed comes from Pangolin’s dex on the Avalanche network.

=CRYPTODEXPRICE(“ETH”,”WAVAX”,”PNG”)

SUSHISWAP DEX PRICES

The price feed comes from sushiswap’s exchange on the ethereum network.

=CRYPTODEXPRICE(“YFI”,”WETH”,”SUSHI”)

QUICKSWAP DEX PRICES

The price feed comes from quickswap’s exchange on the Matic/Polygon network.

=CRYPTODEXPRICE(“WMATIC”,”USDC”,”QUICK”)

SPOOKYSWAP DEX PRICES

The price feed comes from spookyswap’s exchange on the Fantom network.

=CRYPTODEXPRICE(“WFTM”,”ETH”,”BOO”)

CELO SUSHISWAP DEX PRICES

The price feed comes from sushiswap’s exchange on the Celo network.

=CRYPTODEXPRICE(“BTC”,”CUSD”,”CELO”)

BY CONTRACT ADDRESS

You can also request pricing by contract address

Getting prices by contract address

=CRYPTODEXPRICE(contract address 1, contract address 2, exchange ticker)

Here’s how to retrieve the COMP-WETH pair price from Uniswap using Contract Addresses:

COMPOUND (COMP) CONTRACT ADRESS: 0xc00e94cb662c3520282e6f5717214004a7f26888

WRAPPED ETH (WETH) CONTRACT ADRESS
0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2

=CRYPTODEXPRICE(“0xc00e94cb662c3520282e6f5717214004a7f26888”,”0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2",”UNI”)

COINGECKO PRICES

For those of you who are interested in getting fiat-crypto pairs, I wrote the same type of medium for how to get prices from Coingecko in your Google Sheets

CoinGecko Prices, Volumes, Market Caps in Google Sheets and Excel

For anyone looking to extract automatically data from CoinGecko.

medium.com

CODE SETUP:

You have 2 options to access the code:

1 — Easy, you make a copy of the template sheet. When you do, it will automatically save a new template sheet with the code.

2 — Some copy/paste involved. You need to copy/paste the following code in the app script of the Google Sheet you wish to use.

Code available Here

More details can be find in the following medium

Google Sheet Open Source CryptoTools Set-Up

Objective: Show users how they can easily access the custom built-in CryptoTools formulas in Google Sheet:

medium.com

CONCLUSION:

This medium shows you how to retrieve prices in Google Sheet for any cryptocurrency pair using their tickers or contract addresses. Formula looks like:
=CRYPTODEXPRICE(Token1, Token2, Exchange)

=CRYPTODEXPRICE(“COMP”, “WETH”, “SUSHI”)

GOOGLE SHEET EXAMPLE HERE

Exchanges Currently Available:

UNI (Uniswap), CAKE (Pancakeswap V1 Price),CAKEV2 (Pancakeswap V2 Price), SUSHI(Sushiswap), 1INCH (1INCH)

Refreshing

Values are cached for 10min. My server updates prices every 2 minutes but for optimization on API calls on Google Sheets, data is refreshed on a 10min schedule. This is why you’ll notice a small lag on prices. It can be modified directly in the code.

This is ongoing work. If you find errors, please do not hesitate to let me know. Feedback is very welcomed. A telegram chat is also available for support. If this project adds any value, CryptoTools is on patreon.

Thank you so much for investing your time in reading this article.

Take care of yourself & your family in these challenging times!

Join Coinmonks Telegram group and learn about crypto trading and investing

Also, Read

Get Best Software Deals Directly In Your Inbox