The idea
As mentioned in the post on COMEX gold future prediction using Python, the idea of designing the dashboard is to provide an interactive and intuitive view of the COMEX gold future prices which is supported by the Python program in the other post.
The process
During the process of creating the dashboard, to ensure that the dashboard connects and reflects the gold future price predictions in Python in a timely basis, there were some issues encountered.
The first is that Colab cannot be connected to Data Studio directly. Thus there should be another platform serving as the bridge to connect with both Colab Python program and the dashboard in Data Studio. There’re two main platforms possible that can store the data source and be the linkage, Google Sheets and Bigquery.
Since a project creation is a must to use Bigquery as the data source and more Python codes will be used, to simplify the process of transfering and storing the data, by making use of the uniqueness of Google Sheet ID, Google Sheets is considered and used here.
In order to transfer the data output, some codes in Python is added to export the output. To start with, two dataframes containing historical COMEX gold future price and predictions are created.
# Create historical price output dataframe date = test_df2[:-actualdays+predictdays] date = date.reset_index() date = date['DATE'] gld_df = pd.merge(date, gld, how='left', on = ['DATE']) gld_df.fillna(method='ffill', inplace=True) gld_df.tail(10) # Create prediction data output to dataframe pre_df = pre_df_addetf.reset_index() # Change the date column type pre_df['DATE'] = pre_df['DATE'].dt.strftime('%Y-%m-%d') gld_df['DATE'] = gld_df['DATE'].astype(str) pre_df['DATE'] = pre_df['DATE'].astype(str) # Rename the price column gld_df = gld_df.rename(columns={'gld': 'Historical Price'}) pre_df = pre_df.rename(columns={0: 'Prediction'}) pre_df
Then the Google Sheet is created in the Google drive. Since each Google Sheet has its unique ID stored in URL, the ID of the Google Sheet created is set up in the coding to ensure each refreshness update the same Google Sheet which connects with the dashboard eventually. After that, I authorized the google cloud to access the data and export the two dataframes to two sheets inside the Google Sheet created earlier.
sheet_id = '19M9WiF4MKqg2-WNimUpgTKzG-Bx3trtbHjZJ5fUVrmU' worksheet_histo_name = 'Historical Data' worksheet_pred_name = 'Predictions' from google.colab import drive drive.mount('/content/drive') # authenticate from google.colab import auth auth.authenticate_user() #import gspread import gspread from google.auth import default creds, _ = default() gc = gspread.authorize(creds) sh = gc.open_by_key(sheet_id) sh.worksheets() histo_price_sheet = sh.worksheet(worksheet_histo_name) pred_price_sheet = sh.worksheet(worksheet_pred_name) # Clear the sheet data histo_price_sheet.clear() # Update with the latest historical prices within the prediction range histo_price_sheet.update([gld_df.columns.values.tolist()] + gld_df.values.tolist()) # Clear the sheet data pred_price_sheet.clear() # Update with the predictions within the prediction range pred_price_sheet.update([pre_df.columns.values.tolist()] + pre_df.values.tolist())
By doing so, the output is exported and stored in the Google Sheet successfully. Every time the coding program is run, the content in the sheets will be cleared and updated with the most up-to-date COMEX gold future prices and predictions.
With the Google Sheet storing the dataset, I connected the dashboard in Data Studio with the Google Sheet. Hence, the Colab Python program is linked with the dashboard in Data Studio at last.
The design
In short, the dashboard is designed to include the following information to provide insights:
- Current date and date range selection
- Prediction statistics
- Comparision between the historical gold future prices and the predictions
Next, there will be a walkthrough in details. To start with, in order to achieve the timeliness of the predictions, the current date is stated on the left-upper side of the dashboard. Additionally, the date range is also available so that the user can select certain period of dates.
When it comes to the prediction statistics, the highest, lowest and average predicted prices are provided.
Moreover, a calculated field is created in the table with the formula below so that the user can have a idea about the percentage change of the daily predictions. Also, if the percentage change is positive, which means the gold future price is estimated to increase in the next day, the percentage number is in green. Otherwise, the value will be in red to present the estimated decrease.
Besides the statistical numbers, the percentage changes are also visualized in the bar chart on the right side of the table. This bar chart is created so that the user can know about the movements of the price prediction percentage change in the time period.
Last but not least, a line graph of the historical prices and the predictions is added below the table, which is designed for the two main purposes of the dashboard: 1. visualization of the predited trend of COMEX gold future price so that investors can develop trading ideas; 2. how much the predictions fit the historical prices and this help investors identify how much they may rely on the predictions to develop insights.
To Improve
As mentioned before, the dashboard is designed to provide instant and intutitive view of the COMEX gold future predictions which derived from the Python program created. Hence, whenever the users intend to obtain the latest predictions from the most recent historical data, all they need to do is to click the botton to run all Python codes and then click refreshing the data in the dashboard.
- Run the Python Program in Colab
- Refresh the data in Data Studio
What may be improved is to automate the Python program since the dashboard is already set up to be refreshed with the data source every 15 minutes. The reason why the auto-running of the Python program is not set up is that the Google authorization will be required every time running the codes so that the Python output can be authorized by the user to write and update in the Google Sheets. However, this Python program and dashboard can be refreshed automatically with local Python file connected with Tableau. For the sharing purpose here, Data Studio is used here thus this approach is not taken.