Coinmarket API + Google Sheets + Lunarcrush API

Technical Task (TT) for Monitoring Sharp Increases in Cryptocurrencies by Volume and Engagement

Project Goal

Create tables for analyzing and monitoring data for 50–100 cryptocurrencies with the ability to scale up to 250 currencies in one Google Sheet or by adding new documents. The main task is to record and analyze sharp increases in trading volume and engagement, using average values over different time periods.

The tables should be dynamic, highlighting cryptocurrencies with sharp changes (for example, a 50% increase in the last 3 hours) or moving them to the top of the table.

Functional Requirements

1. Volume Data

   • Average Values:
     • Average daily volume for the last 30 days.
     • Average daily volume for the last 7 days.
     • Average values for 3 days, 2 days, and yesterday.
   • Changes:
     • Volume for the last 3 hours (hourly data).
     • Volume for the last 6 hours (aggregated data).
     • Volume for the last 12 hours (aggregated data).

2. Engagement Data (LunarCrush)

   • Average Values:
     • Average daily engagement for the last 30 days.
     • Average daily engagement for the last 7 days.
     • Average values for 3 days, 2 days, and yesterday.
   • Changes:
     • Engagement for the last 3 hours, 6 hours, 12 hours.

3. Analysis and Highlighting

   • Formulas for Analysis:
     • Percentage change for 3, 6, 12 hours relative to average values.
     • If the change >= 50%, the row is automatically highlighted or moved to the top of the table.
   • Data Highlighting:
     • Red color for sharp declines.
     • Green color for sharp increases.
   • The table should be user-friendly for monitoring.

4. Scalability

   • Information about 50–100 cryptocurrencies is stored in one table.
   • Ability to:
     • Add new rows for currencies within one table (up to 250 rows).
     • Create new tables for other currencies using a ready-made template.

Technical Requirements

Python

1. Data Collection:
   • API CoinMarketCap for volumes:
     • Metrics: trading volume (volume_24h), market capitalization (market_cap).
   • API LunarCrush for engagement:
     • Metrics: engagement, mentions, social_volume.
2. Data Updates:
   • Hourly updates.
   • Storing data in Google BigQuery.
3. Aggregation:
   • Script for calculating average values:
     • Averages for 30 days, 7 days, 3 days, 2 days, and yesterday.
     • Hourly data for the last 12 hours.

BigQuery

1. Tables:
   • LiveData:
     • Stores data for the last 7 days (hourly records).
   • AggregatedData:
     • Stores daily aggregates for 30 days.
2. Queries:
   • Calculating average values for different periods.
   • Data extraction for Google Sheets.

Google Sheets

1. Connection:
   • Google Data Connector for pulling data from BigQuery.
2. Formulas:
   • Percentage change:

     excel=IF(B2<>0, (C2-B2)/B2, 0)
     
     

     Where B2 is the average value for 30 days, C2 is the current volume for the last 3 hours.
   • Highlighting changes:
     • Conditional formatting for changes >= 50%.
3. Template:
   • Table with:
     • Hourly data.
     • Average values for 30, 7, 3, 2 days, and yesterday.
     • Dynamic row highlighting.

Implementation Stages

1. Data Collection (1 day):

   • Setting up a Python script to collect data from the CoinMarketCap and LunarCrush APIs.

2. Data Updates in BigQuery (1–2 days):

   • Creating tables LiveData and AggregatedData.
   • Setting up a script to upload data to BigQuery.

3. Integration with Google Sheets (1 day):

   • Setting up the Data Connector.
   • Creating the first table for 50–100 currencies.

4. Formulas and Highlighting (1 day):

   • Adding formulas for calculating percentage changes.
   • Setting up conditional formatting.

5. Scalability (1 day):

   • Documentation for adding new currencies.
   • Template for creating new tables.

Completion Criteria

1. Table with data for 50–100 cryptocurrencies:
   • Hourly updates.
   • Average values and hourly changes.
   • Highlighting sharp changes (increase >= 50%).
2. Ability to add currencies to the current table (up to 250 rows).
3. Ease of creating new tables.

Execution Timeline

4–6 days, if all accesses and keys are provided on time.

If additional clarifications or details are needed, let me know!