How to Build an Online Database APP by Python, PySide6 and Google Sheets (13): Dashboard

Previously

How to Build an Online Database APP by Python, PySide6 and Google Sheets (12): View Settings

This article is about how to create dashboard in the app:

Constructor and UI Initialization:

• The Dashboard class initializes with the Google Sheets client and sets up the user interface in the initUI method.
• A QVBoxLayout is used for the overall layout, with two QGridLayouts for organizing different metrics.

class Dashboard(QWidget):
    def __init__(self, google_sheets_client):
        super().__init__()
        self.google_sheets_client = google_sheets_client
        self.initUI()

    def initUI(self):
        layout = QVBoxLayout(self)

        # Use QGridLayout for the first row
        row1_layout = QGridLayout()

        # Add group boxes to the first row
        row1_layout.addWidget(self.create_group_box("Total Assets Under Management", self.calculate_aum), 0, 0)
        row1_layout.addWidget(self.create_group_box("Active Investors", self.fetch_active_investors), 0, 1)
        layout.addLayout(row1_layout)

        # Row 2: Detailed Metrics
        row2_layout = QGridLayout()
        row2_layout.addWidget(self.create_group_box("New Investments this Quarter", self.fetch_quarterly_new_investments), 0, 0)
        row2_layout.addWidget(self.create_group_box("Withdrawals this Quarter", self.fetch_quarterly_withdrawals), 0, 1)
        row2_layout.addWidget(self.create_group_box("Dividend Amount last Quarter", self.fetch_quarterly_dividend_amount), 0, 2)
        row2_layout.addWidget(self.create_group_box("New Investors this Quarter", self.fetch_new_investors_this_quarter), 0, 3)

        layout.addLayout(row2_layout)
        self.setLayout(layout)

Group Box Creation:

• create_group_box: This method creates individual group boxes for each metric. It calls a data callback function to fetch the relevant data and displays it in a QLineEdit widget, which is set to be read-only for display purposes.
• The appearance of the widgets is customized using stylesheets and fonts, with different styles for different types of metrics.

    def create_group_box(self, title, data_callback):
        group_box = QGroupBox(title)
        group_box.setAlignment(Qt.AlignmentFlag.AlignCenter)
        layout = QVBoxLayout(group_box)

        data = data_callback()  # Get data using the callback function
        for label, value in data:
            widget = QLineEdit(str(value))
            widget.setReadOnly(True)
            widget.setAlignment(Qt.AlignmentFlag.AlignCenter)
            widget.setStyleSheet("background: transparent; border: none;")
            if label in ["AUM", "Active Investors"]:
                widget.setFont(QFont("Arial", 50, QFont.Weight.Bold))
                widget.setStyleSheet("color: green; background: transparent; border: none;")
            elif label in ["New Investments this Quarter", "Withdrawals this Quarter", "Dividend Amount last Quarter", "New Investors this Quarter"]:
                widget.setFont(QFont("Arial", 30, QFont.Weight.Bold))
                widget.setStyleSheet("color: red; background: transparent; border: none;")

            layout.addWidget(widget)
        
        return group_box

Data Retrieval Methods:

These following methods fetch data from various sheets in Google Sheets (like “05_Balances”, “03_Transactions”, “04_Dividends”) and calculate the metrics to be displayed. They handle data extraction, aggregation, and formatting.

• calculate_aum

    def calculate_aum(self):
        # Fetch data from the "05_Balances" sheet
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("05_Balances")
        all_data = worksheet.get_all_values()
        headers = all_data[0]
        current_quarter = self.get_current_quarter()

        balance_start_index = headers.index("Balance at Start")
        new_account_balance_index = headers.index("New Account Balance")
        period_index = headers.index("Dividend Period")

        total_aum = sum(float(row[balance_start_index] or row[new_account_balance_index] or 0)
                        for row in all_data[1:] if row[period_index] == current_quarter)
        
        # Convert total_aum to an integer and format with thousands separator
        #total_aum_int = int(total_aum)
        total_aum_formatted = f"${int(total_aum):,}"

        return [("AUM", total_aum_formatted)]

• fetch_active_investors

    def fetch_active_investors(self):
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("05_Balances")

        # Get all data from the worksheet
        all_data = worksheet.get_all_values()

        # Get the header row and column indices
        header_row = all_data[0]
        dividend_period_index = header_row.index("Dividend Period")
        balance_at_start_index = header_row.index("Balance at Start")
        investor_id_index = header_row.index("ID_Investor")

        # Get the current quarter
        current_quarter = self.get_current_quarter()  # You need to implement this function

        # Filter rows where "Dividend Period" is the current quarter and "Balance at Start" is not 0
        active_investors_rows = [
            row for row in all_data[1:]
            if row[dividend_period_index] == current_quarter and row[balance_at_start_index] != "0"
        ]

        # Count the number of unique investor IDs
        unique_investor_ids = set(row[investor_id_index] for row in active_investors_rows)
        num_active_investors = str(len(unique_investor_ids))

        return [("Active Investors", num_active_investors)]

• fetch_quarterly_new_investments

    def fetch_quarterly_new_investments(self):
        # Fetch data from the "03_Transactions" sheet
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("03_Transactions")
        all_data = worksheet.get_all_values()
        headers = all_data[0]
        current_quarter = self.get_current_quarter()

        # Find the column indices for "Dividend Period", "Money In/Out", and "Amount"
        dividend_period_index = headers.index("Dividend Period")
        money_in_out_index = headers.index("Money In/Out")
        amount_index = headers.index("Amount")

        # Calculate the sum of "Amount" for "Money In" transactions in the current quarter
        total_money_in = sum(
            float(row[amount_index] or 0) for row in all_data[1:]
            if row[dividend_period_index] == current_quarter and row[money_in_out_index] == "Money In"
        )

        # Format the total with thousands separator and return
        total_money_in_formatted = f"${int(total_money_in):,}"
        return [("New Investments this Quarter", total_money_in_formatted)]

• fetch_quarterly_withdrawals

    def fetch_quarterly_withdrawals(self):
        # Fetch data from the "03_Transactions" sheet
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("03_Transactions")
        all_data = worksheet.get_all_values()
        headers = all_data[0]
        current_quarter = self.get_current_quarter()

        # Find the column indices for "Dividend Period", "Money In/Out", and "Amount"
        dividend_period_index = headers.index("Dividend Period")
        money_in_out_index = headers.index("Money In/Out")
        amount_index = headers.index("Amount")

        # Calculate the sum of "Amount" for "Money In" transactions in the current quarter
        total_money_out = sum(
            float(row[amount_index] or 0) for row in all_data[1:]
            if row[dividend_period_index] == current_quarter and row[money_in_out_index] == "Money Out"
        )

        # Format the total with thousands separator and return
        total_money_out_formatted = f"-${int(total_money_out):,}"
        return [("Withdrawals this Quarter", total_money_out_formatted)]    

• fetch_quarterly_dividend_amount

    def fetch_quarterly_dividend_amount(self):
        # Fetch data from the "04_Dividends" sheet
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("04_Dividends")
        all_data = worksheet.get_all_values()
        headers = all_data[0]
        current_quarter = self.get_current_quarter()

        last_quarter = self.get_last_quarter(current_quarter)  # You need to implement this function to get the last quarter

        # Find the column indices for "Dividend Period" and "Dividend Amount"
        dividend_period_index = headers.index("Dividend Period")
        dividend_amount_index = headers.index("Dividend Amount")

        # Calculate the sum of "Dividend Amount" for the last quarter
        total_dividend_amount = sum(
            float(row[dividend_amount_index] or 0) for row in all_data[1:]
            if row[dividend_period_index] == last_quarter
        )

        # Format the total with thousands separator and return
        total_dividend_amount_formatted = f"${int(total_dividend_amount):,}"
        return [("Dividend Amount last Quarter", total_dividend_amount_formatted)]

• fetch_new_investors_this_quarter:

    def fetch_new_investors_this_quarter(self):
        worksheet = self.google_sheets_client.open_spreadsheet(GOOGLESHEET_ID).worksheet("05_Balances")

        # Get all data from the worksheet
        all_data = worksheet.get_all_values()

        # Get the header row and column indices
        header_row = all_data[0]
        dividend_period_index = header_row.index("Dividend Period")
        new_account_balance_index = header_row.index("New Account Balance")

        # Get the current quarter
        current_quarter = self.get_current_quarter()  # Assuming this function is already implemented

        # Filter rows where "Dividend Period" is the current quarter and "New Account Balance" is not empty
        new_investors_rows = [
            row for row in all_data[1:]
            if row[dividend_period_index] == current_quarter and row[new_account_balance_index] != ""
        ]

        # Count the number of new investors
        num_new_investors = len(new_investors_rows)

        return [("New Investors this Quarter", str(num_new_investors))]

• get_current_quarter and get_last_quarter: These helper methods calculate the current and previous quarters, which are crucial for filtering data based on time periods.

    def get_current_quarter(self):
        # Logic to determine the current quarter
        current_month = datetime.now().month
        quarter = (current_month - 1) // 3 + 1
        current_year = datetime.now().year
        return f"{current_year}Q{quarter}"

    def get_last_quarter(self, current_quarter):
        # Logic to determine the last quarter
        year = int(current_quarter[:4])
        quarter = int(current_quarter[-1])

        if quarter == 1:
            year -= 1
            quarter = 4
        else:
            quarter -= 1

        return f"{year}Q{quarter}"

All-in-One

How to Build an Online Database APP by Python, PySide6 and Google Sheets (All-in-One)