🪙 It is a fun and useful project that I created. This Excel-based tool can make cryptocurrency portfolio tracking easy and interactive. It's like a financial playground for crypto enthusiasts and data lovers. It's powered by real-time data from the CoinMarketCap API. It shows only a fraction of the possibilies which can be achieved, like analysis of past prices or gathering information about crypto trends.
🗺️ To kick things off, I needed a reliable source of crypto data, and that's where CoinMarketCap's API comes into play. There is a basic free plan which has enabled me to access the API which later will provide me necessary data. TO get the access I had to register to the developer portal.
🔗 I began by selecting the appropriate API endpoint based on CoinMarketCap's documentation. This crucial step ensures that the dashboard fetches the exact data I need.
🌐 I then opened the "Data From Web" option in Excel, filling in the necessary information to connect to the CoinMarketCap API accordingly to the guideline in the documentation. This integration seamlessly facilitates the continuous flow of data between the API and the future dashboard.
📊 In this step, I utilized Power Query within Excel to prepare the data for analysis. Here's how I went about it:
- Convert Inputs to Table: I initiated the process by converting the raw inputs into a structured table format.
- Choose Necessary Columns: Next, I selected the required columns from the available dataset. By doing so, I focused the analysis on the specific information I needed, simplifying the subsequent steps. For the purpose of this excercise I have choosen 4 Cryptocurrencies to use later. Bitcoin, Ethereum, Cardano and XRP.
- Set the Format of the Columns: To ensure the data is correctly interpreted, I applied appropriate formatting to the selected columns.
- Create a Table in Excel: After shaping the data in Power Query, I brought it into Excel as a structured table. This final table is the foundation for the subsequent analysis and visualization.
🧰 I have created additional excel table which contains all of the necessary data for the dashboard. It pulls the current price from API data and thanks to the robust formulas and logic calculates current portfolio value and profit.
📈 Finally a summary section provides key metrics, including total investment, current portfolio value, profit/loss percentage, and more. These metrics offer a snapshot of the portfolio's health and performance. Additionally I have added a refresh button with a macro for easy update of data.
