【Old Cat Tests 3C】Investment Side Gig Tool! Master US Stocks, Taiwan Stocks, and Cryptocurrencies with Google Forms

This article is reproduced from Lao Mao Tests 3C. The author is Josh Lu Zijian, and the original title is "Essential Financial Management Tools in the Coin Circle, One-Click Cross-Exchange Tracking of All Profits and Losses, Stock Market Tracking Together."

In the field of cryptocurrency investment, due to the diversity of currencies, there are currently over 6,000 types listed on Coinmarketcap. In order to diversify investments, investors usually open several exchanges to purchase different types of cryptocurrencies (each exchange lists different types of currencies), and also participate in IEOs on different exchanges, making asset management a challenge.

Although there are many cryptocurrency asset management apps on the market, they often cannot achieve optimal management. We can see that even some well-known KOLs still use Excel to manage assets. Today, we will use Google Sheets to create a tool that can update coin prices in real-time, allowing everyone to create their own customized financial management tools.

Advertisement - Please scroll down for more content

Why Use Google Sheets to Track Assets When There Are Cryptocurrency Management Apps Available?

Currently, cryptocurrency management apps on the market have performed well, as they can integrate multiple exchanges. Users can understand the asset status across different exchanges within a single app, observing overall asset changes and current coin holdings. However, why would one still need to use Google Sheets to track assets?

Firstly, users may require more detailed information. Users are concerned about the overall asset changes and individual profits and losses from each trade. Being able to promptly confirm if certain coins have reached the loss limit and exit immediately is crucial, or being able to know instantly when profits have reached the target and set stop-loss or trailing stop to protect profits is also very important. These two points cannot be achieved in a cryptocurrency management app, so Google Sheets come in handy for these specific needs.

Tracking Profits and Losses Across Exchanges for Every Trade

For an ordinary trader, the most common issue is the need to open different exchange apps to confirm the latest profit and loss status for each trade. This inconvenience becomes more apparent if you have a wider range of investments.

Imagine having to open the stock market app to see your stock market profits and losses, then opening several cryptocurrency exchange apps to check profits and losses, followed by opening a fund app to view the investment fund returns. This process is time-consuming. In the world of cryptocurrencies, frequent trading is common. Although you can see the current profit and loss for each trade by opening the app, due to frequent trading, you might have to scroll through the app for a long time to find the profit and loss status from a trade made a week ago, which is very inconvenient.

Manually recording the amount and price of each entry on Google Sheets, along with real-time coin price tracking, allows you to quickly see the investment performance in each coin, which can also be applied to investment products such as stocks and funds.

Lazy Data Crawler on Google Sheets

Even if you don't know how to code, you can easily fetch data through Google Sheets. Google Sheets offers several tools to help us import data into spreadsheets using functions:

IMPORTDATA – Read CSV / TSV data from the internet
IMPORTHTML – Read table data from a webpage
IMPORTXML – Read any content from a webpage
Here we use IMPORTXML to fetch real-time data. The syntax is as follows:

Syntax
IMPORTXML URL, XPath_Query

By providing the data URL and XPath, you can fetch the data. Here, we fetch coin prices from CoinMarketCap, where the XPath can be a bit tricky. Each data on a webpage has its own address, and the XPath is the address of the coin price. How do you fetch it?

1. Right-click on the target data and select Inspect

2. Right-click on the target data area on the right side again, then select copy -> copy XPath

This way, we obtain the XPath as follows:
//*[@id="__next"]/div/div[2]/div[1]/div[2]/div[1]/div/div[1]/span[1]/span[1]

Use the following syntax:
IMPORTXML URL, XPath_Query

IMPORTXML "https://coinmarketcap.com/zh-tw/currencies/bitcoin", "//*[@id='__next']/div/div[2]/div[1]/div[2]/div[1]/div/div[1]/span[1]/span[1]"

Please note the red part above: //*[@id="__next"]. The original XPath uses double quotes, but here it should be changed to single quotes to function properly: //*[@id='__next']

For easier modification of URLs next time, we can modify the usage as follows, allowing direct modification of the content in cell G2:
=IMPORTXML(G2, "//*[@id='__next']/div/div[2]/div[1]/div[2]/div[1]/div/div[1]/span[1]/span[1]")

The final product after completion is shown below, with the red area being the manually entered section. Once data is input, you can instantly update coin prices and track the profit and loss of your assets at any time.

Not Just Cryptocurrencies, Stocks, Funds, and Currencies Are Also Applicable

Google Sheets can be used not only to track cryptocurrencies but also stocks, funds, and forex currencies all in one chart. You don't need to open each app individually to confirm the overall asset status. The Google Finance feature supports information on stock markets, funds, and currency trading.

Example for Taiwan Stock Exchange: Yuanta Financial Holdings

Syntax:

=GOOGLEFINANCE("TPE:2884", "price")

Example for US Stock Market: Netflix

Syntax:

=GOOGLEFINANCE("NASDAQ:NFLX", "price")

With this single chart, you can simultaneously track stock and cryptocurrency profits and losses. Convenient, isn't it?

Isn't Google Sheets' functionality great? Start creating your customized asset tracking sheet today! If you find it a bit challenging, feel free to join my Telegram group for discussions and to receive a simple financial tool ->https://t.me/twmcovisa