介紹 (Introduction)

This article will show you one of the ways you can process stock price data using Google Cloud Platform’s BigQuery, and build a simple dashboard on the processed data using Google Data Studio.

本文將向您展示使用Google Cloud Platform的BigQuery處理股價數據以及使用Google Data Studio在處理后的數據上構建簡單儀表板的一種方法。

Learning to do so can be especially useful for anyone who wishes to automate the findings from stock price insights, and is looking for an efficient and fast way to store the whole process on a cloud platform.

學會這樣做對于希望自動化股價洞察力發現的任何人特別有用，并且他們正在尋找一種高效，快速的方法來將整個過程存儲在云平臺上。

This article is meant to act as a continuation, or ”part 2", to a previous article in which I showed How to automate financial data collection with Python using APIs and Google Cloud. Feel free to give it a read if you are interested in the upstream data import and script automation side of this workflow. If not, just skip and read on.

本文旨在作為上一篇文章的續篇或“第2部分”，在上一篇文章中，我展示了 如何使用API??和Google Cloud使用Python自動執行財務數據收集 。 如果您對以下內容感興趣，請隨時閱讀此工作流程的上游數據導入和腳本自動化方面，否則請跳過并繼續閱讀。

第1步：確定BigQuery的數據源 (Step 1: Identify BigQuery’s Data Sources)

GoogleBigQuery is GoogleCloud’s data warehousing solution (one of the many) and quite ideal for working with relational data such as those in this tutorial.

GoogleBigQuery是GoogleCloud的數據倉庫解決方案(眾多解決方案之一)，非常適合處理本教程中的關系數據。

In part 1, I illustrated how you can automate the data feeds into BigQuery using Cloud Functions. In this next step, you are going to be using the same data sources (daily stock price data from S&P500 firms, as well as related mapping tables which will allow us to enrich the data with some categorical variables ) to build a simple & neat processing and data visualization pipeline.

在第1部分中，我說明了如何使用Cloud Functions自動化BigQuery中的數據饋送。 在下一步中，您將使用相同的數據源(來自S＆P500公司的每日股價數據，以及相關的映射表，這些映射表將使我們能夠使用一些分類變量來充實數據)來構建簡單整潔的處理程序和數據可視化管道。

N.B. The following screenshots will be taken from my own GCP Console (which I have set up with Italian as a default language). I have documented each screenshot with explanations so that everyone is able to follow along in English.

注意：以下屏幕截圖將來自我自己的GCP控制臺(我將意大利語設置為默認語言)。 我已經記錄了每個屏幕快照的解釋，以便每個人都可以用英語跟隨。

To get started, once logged into BigQuery’s editor and, provided you have set up a dataset, you can identify the uploaded data sources by simply clicking on the “Resources” tab on the left side of the editor’s page.

首先，登錄到BigQuery的編輯器，并在設置好數據集的情況下 ，只需單擊編輯器頁面左側的“資源”標簽，即可識別上載的數據源。

This allows you to immediately get a list of your datasets and the tables included in each dataset.

這使您可以立即獲取數據集列表以及每個數據集中包含的表。

For this exercise, my data warehouse structure is as follows(I am going to ignore the USA_SectorViews table reported in the above screenshot):

對于本練習，我的數據倉庫結構如下(我將忽略上面截圖中報告的USA_SectorViews表)：

Datasets: csm

數據集 ： csm

Tables:

桌子 ：

1. SPcomponents: A table which identifies the complete list of S&P500 member firms (Source: List of S&P 500 companies)

   SPcomponents ：一個表，該表標識了S＆P500成員公司的完整列表(來源： S＆P 500公司列表 )

The majority of the columns in this table are exactly as reported in the above source link, so you can directly use that web-page as a reference.

該表中的大多數列與上面的源鏈接中的報告完全相同，因此您可以直接使用該網頁作為參考。

2. SPhistorical: A table containing daily stock price information for all S&P500 member firms, from 2000 until ~June 2020.

2.歷史記錄 ：該表包含2000年至2020年6月期間所有S＆P500成員公司的每日股價信息。

步驟2：計算股票指標并在已保存SQL查詢中合并類別變量 (Step 2: Calculate Stock Metrics and merge categorical variables in a saved SQL Query)

Using the above two tables, let’s process the data using BigQuery’s SQL editor to derive a comprehensive table featuring both stock price indicators and categorical variables.

使用上面的兩個表，讓我們使用BigQuerySQL編輯器處理數據，以得出包含股票價格指標和分類變量的綜合表。

For the purpose of this example, the final output table will feature the following columns:

就本示例而言，最終輸出表將包含以下列：

Timestamp: the date key of each row and stock

時間戳 ：每行和股票的日期鍵

Symbol: the stock identifier for each S&P500 firm

符號 ：每個S＆P500公司的股票標識符

GICS_Sector: Column indicating the industry for each firm (Health Care, Consumer, etc..)

GICS _Sector：此列表示每個公司的行業(醫療保健，消費者等)。

Headquarters: the HQ location of each firm

總部： 每個公司的總部位置

Percentage_Daily_Return: the daily return of each stock (based on the Close price)

Percentage_Daily_Return：每只股票的每日收益(基于收盤價)

MA_5_days: The stock’s moving average for the previous 5 days, where the period’s reference is the current row’s date. Based on Close price.

MA_5_days：前5天的股票移動平均線，其中期間的參考是當前行的日期。 基于收盤價。

MA_10_days: The stock’s moving average for the previous 10 days, where the period’s reference is the current row’s date. Based on Close price.

MA_10_days：過去10天的股票移動平均線，其中期間的參考是當前行的日期。 基于收盤價。

MA_15_days: The stock’s moving average for the previous 15 days, where the period’s reference is the current row’s date. Based on Close price.

MA_15_days：過去15天的股票移動平均線，其中期間的參考是當前行的日期。 基于收盤價。

The period choice for calculating moving averages has no intrinsic reason and was just computed for the sake of this walk-through. You can definitely learn more about moving averages online, as there are plenty of valuable tutorials.

計算移動平均線的周期選擇沒有內在原因，只是為了進行本演練而進行了計算。 您肯定可以在線了解更多有關移動均線的信息，因為這里有很多 有價值的教程 。

Using our two tables, you can see that you have most columns (Timestamp,Symbol,GICS_Sector,Headquarters) ready to go already.

使用我們的兩個表，您可以看到大多數列(Timestamp，Symbol， GICS _Sector，Headquarters)已經準備就緒。

Using the Close price column in the SPhistorical table, you can calculate the remaining columns (Percentage_Daily_Returns and the stock’s moving averages across 5–10–15 day periods).

使用SPhistorical表中的“ 收盤價”列，您可以計算剩余的列(Percentage_Daily_Returns和股票在5–10–15天期間的移動平均線)。

First, let’s calculate the daily return for each stock. A stock’s return is calculated as the difference in Close prices between any two days, expressed as a percentage of the previous day’s Close.

首先，讓我們計算每只股票的每日收益。 股票的收益以任意兩天收盤價之差計算，以前一天收盤價的百分比表示。

ROUND(((CAST(Close AS float64)-CAST(LAG(Close,1) OVER(PARTITION BY p.Symbol ORDER BY Timestamp) AS float64))/CAST(Close AS float64))*100,2) AS Percentage_Daily_Return

You can use the LAG function to identify the previous daily Close (for each ticker symbol only, as you do not want to calculate returns based on different stock prices; hence the use of OVER(PARTITION BY Symbol)), take its difference with the current day’s Close, and divide it by the Close price for that particular day to calculate the return.

您可以使用LAG函數來識別以前的每日收盤價(僅針對每個股票代號，因為您不想基于不同的股票價格計算收益；因此使用OVER(PARTITION BY Symbol))將其與當天的收盤價，然后將其除以該特定日期的收盤價即可計算出收益。

This is the essence of the calculation. You can then use the CAST function to convert text data types to floats, in order to be able to add and divide numbers, and the ROUND function to round returns to just two decimals.

這是計算的本質。 然后，您可以使用CAST函數將文本數據類型轉換為浮點數，以便能夠對數字進行加法和除法，而ROUND函數的舍入運算僅返回兩位小數。

You can avoid using the CAST function if your data types are already the correct ones (i.e. numeric for numbers, etc..)

如果您的數據類型已經是正確的類型(例如，數字代表數字等)，則可以避免使用CAST函數。

The stock’s moving averages across 5-day periods are then calculated as:

然后將股票在5天期間的移動平均值計算為：

AVG(CAST(Close AS float64)) OVER (PARTITION BY p.Symbol ORDER BY Timestamp ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS MA_5day

you can use the AVG function to compute the mean of the Close price for each different Symbol, considering a row window made up of the current day’s Close and the previous 4 days making up the 5 day time period.

您可以使用AVG函數來計算每個不同交易品種的收盤價平均值，同時考慮由當天的收盤價和構成5天時間段的前4天組成的行窗口。

The same logic is replicated across 10 and 15 day periods.

在10天和15天的時間段內重復相同的邏輯。

AVG(CAST(Close AS float64)) OVER (PARTITION BY p.Symbol ORDER BY Timestamp ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS MA_10daysAVG(CAST(Close AS float64)) OVER (PARTITION BY p.Symbol ORDER BY Timestamp ROWS BETWEEN 14 PRECEDING AND CURRENT ROW) AS MA_15days

Putting everything together, you obtain the following SQL query:

將所有內容放在一起，將獲得以下SQL查詢：

In summary, the query pulls from both the SPhistorical (aliased as “p”) and SPcomponents table (aliased as “c”). The two tables are joined using the mutual Symbol column as a key.

總而言之，該查詢同時從SPhistorical (別名為“ p ”)和SPcomponent s表(別名為“ c ”)中提取 。 這兩個表使用共同的符號列作為鍵進行連接。

I am using the SPhistorical table as the main reference table between the two. Thus I define a LEFT JOIN via which I bring the categorical variables I am interested in (GICS_Sector & Headquarters)from SPcomponents.

我正在使用SPhistorical表作為兩者之間的主要參考表。 因此，我定義了一個LEFT JOIN，通過它可以從SPcomponents中引入我感興趣的分類變量( GICS_Sector＆Headquarters )。

Timestamp, Symbol, and daily Close are pulled from p. Categorical variables GICS_Sector and Headquarters are pulled from c. On the daily close, the above-explained calculations are computed.

時間戳記 ， 符號和每日關閉均從p拉出。 分類變量GICS_Sector和Headquarters從c提取。 在每日收盤時，將計算上述計算。

The table is then grouped by the relevant variables and ordered by Symbol and Timestamp. You launch the query, wait for BigQuery to compute and execute, and you obtain the results shortly after.

然后將該表按相關變量分組，并按Symbol和Timestamp排序。 您啟動查詢，等待BigQuery計算和執行，然后不久便獲得結果。

You can then either refine your query, compute new metrics and re-run as many times as you like to get to the output you desire. Also, do not forget to save your query as a “Saved Query”, by clicking on the “Save Query” button next to the “Execute” button. Find more info here.

然后，您可以優化查詢，計算新指標并重新運行多次，以獲得所需的輸出。 同樣，不要忘記通過單擊“執行”按鈕旁邊的“保存查詢”按鈕將查詢另存為“保存的查詢”。 在此處查找更多信息。

Once done, you are ready to use the query’s results as the data source layer of your Data Studio dashboard.

完成后，您就可以將查詢結果用作Data Studio儀表板的數據源層。

步驟3：在Data Studio中，連接到保存的查詢，然后將數據拉到儀表板上 (Step 3: From Data Studio, connect to saved Query and pull data onto dashboard)

Option 1:

選項1：

You can now hop onto your Data Studio account and click on the plus sign to start a new report.

現在，您可以跳至Data Studio帳戶 ，然后單擊加號以開始新報告。

From here, you can choose among a variety of data connectors:

在這里，您可以選擇各種數據連接器：

After selecting BigQuery, you can simply click on Import Personalized Query and paste in our Saved Query you built at step 3. Once done, click Add.

選擇BigQuery之后 ，您只需單擊Import Personalized Query并將其粘貼到您在第3步中構建的Saved Query中。完成后，單擊Add 。

Provided that you are connected, the data will be pulled in and you will be then presented with an blank Report view from which you can start building your dashboard. Notice how BigQuery is selected as my data origin.

前提是已連接，將提取數據，然后為您提供空白的報表視圖，從中可以開始構建儀表板。 請注意，如何選擇BigQuery作為我的數據來源 。

Option 2:

選項2：

Within BigQuery, you can click on “Explore Data” > “Explore with Data Studio”.

在BigQuery中，您可以單擊“瀏覽數據”>“使用Data Studio瀏覽”。

By clicking on here, a Data Studio data exploration pane will pop up and you will be able to get started plotting and visualizing the data. This is especially useful to conduct quick analysis and get an immediate visual sense of your query results.

單擊此處，將彈出一個Data Studio數據瀏覽窗格，您將可以開始繪制和可視化數據。 這對于進行快速分析并立即獲得查詢結果的視覺效果特別有用。

步驟4：使用Google Data Studio探索數據 (Step 4: Explore the data with Google Data Studio)

At this step, you may prefer to go with option 1 if your end goal is to build out a full dashboard or visualization report.

在此步驟中，如果最終目標是構建完整的儀表板或可視化報告，則可能更喜歡使用選項1。

I will also opt for this option to gain a bit more flexibility when exploring the data (space exploration functionality is still in beta version at the time of this writing).

在探索數據時，我還將選擇該選項以提高靈活性(在撰寫本文時， 太空探索功能仍處于beta版)。

To get a bit more insights about the data and give an overview of Data Studio’s functionality, I will answer the following questions:

為了獲得有關數據的更多見解并概述Data Studio的功能，我將回答以下問題：

1) What are the main industry sectors making up the S&P500 in 2020?

1)2020年，標準普爾500構成哪些主要行業？

2) How are these firms distributed geographically (in terms of HQ base)?

2)這些公司如何在地理上分布(以總部為基礎)？

3) How has the SPY* performed historically?

3) SPY *的歷史表現 如何 ？

4) How does the latest trend in rolling averages look like for the SPY?

4)SPY的最新滾動平均值趨勢如何？

*Index Fund tracking the S&P500 as a whole. In part 1, I also included the SPY in addition to each S&P500 individual member, and I am going to make use of it here

*跟蹤整個標普500指數基金。 在第1部分中 ，除了每個S＆P500個人成員之外，我還包括SPY，并且我將在這里使用它

Overall, Data Studio acts quite simply as an intuitive drag and drop interface, with the user being able to choose among different chart types and style/format them according the data at their disposal.

總體而言，Data Studio的操作非常簡單，就像一個直觀的拖放界面，用戶可以在不同的圖表類型中進行選擇，并根據可使用的數據對它們進行樣式/格式化。

At this link, you can also reference a great guide which illustrates their entire interface in great detail.

在 此 鏈接上，您還可以參考一份出色的指南，其中詳細介紹了它們的整個界面。

Let’s now see how different chart types can help with answering the above questions.

現在，讓我們看看不同的圖表類型如何幫助回答上述問題。

1) What are the main industry sectors making up the S&P500 in 2020?

1)2020年，標準普爾500構成哪些主要行業？

To tackle question one, a pie chart seems like a good choice. Refer to this quick guide for details on adding charts to your blank report on Data Studio.

為了解決問題，餅圖似乎是一個不錯的選擇。 有關將圖表添加到Data Studio的空白報表中的詳細信息，請參考此快速指南 。

The S&P500 is well balanced across industry sectors. The top 3 in order of prevalence are Industrials, Information Technology and Financials, with making up around a 13–14% share of the total.

S＆P500在各個行業之間保持平衡。 發生率排名前三的是工業，信息技術和金融，占總數的13–14％。

2) How are these firms distributed geographically (in terms of HQ base)?

2)這些公司在地理上如何分布(以總部為基礎)？

In terms of HQ location, one can see a prevalence of US firms, as expected, with some European firms present. When using a map, you can make good use of the toolbar to zoom in&out, entering full screen-mode, and overall adjusting the map view.

就總部所在地而言，正如預期的那樣，可以看到美國公司的盛行，一些歐洲公司也出席了會議。 使用地圖時，您可以充分利用工具欄進行放大和縮小，進入全屏模式以及整體調整地圖視圖。

3) How has the SPY* performed historically?

3) SPY *的歷史表現 如何 ？

Next, I plot the SPY daily Close over the years using a line chart, to get a sense of its trend.

接下來，我使用折線圖繪制多年來SPY的每日收盤價，以了解其趨勢。

In the long term, the SPY goes from around 150 to 300+ in value, providing for a steady increase.

從長遠來看，SPY的價值從150左右增加到300+，可以實現穩定的增長。

4) How does the latest trend in rolling averages look like for the SPY?

4)SPY的最新滾動平均值趨勢如何？

I then plot the 5/10/15 day moving averages in more recent times, to see how they stack against each other. Having chosen similar time scales for the three metric, you can see that in general they tend to track each other quite closely, with the 15-day period average showing a bit more variability around the general trend line.

然后，我繪制最近5月15日的移動均線，以查看它們如何相互疊加。 為這三個指標選擇了類似的時間標度后，您可以看到，總體而言，它們趨向于彼此密切跟蹤，而15天的周期平均值在總體趨勢線附近顯示出更多的可變性。

The period choice for calculating moving averages has no intrinsic reason and was just computed in order to be plotted. You can definitely learn more about moving averages online, as there are plenty of valuable tutorials.

計算移動平均線的周期選擇沒有內在原因，只是為了繪制而進行了計算。 您肯定可以在線了解更多有關移動均線的信息，因為這里有很多 有價值的教程 。

Putting everything together, you can the add a headline and title to you report, and obtain something along the lines of:

將所有內容放在一起，您可以為報告添加標題和標題，并獲得以下內容：

Which allows you to get a complete snapshot of your data and indicators of interest.

這使您可以獲得有關數據和指標的完整快照。

As you can see, Data Studio is quite simple to use and provides great data connectors and interactivity.

如您所見，Data Studio的使用非常簡單，并提供了出色的數據連接器和交互性。

下一步 (Next steps)

Your workflow is officially set up. You can save all of your BigQuery SQL queries and your Data Studio report, and refresh/extend resources as new data comes in.

您的工作流程已正式設置。 您可以保存所有BigQuery SQL查詢和Data Studio報表，并在新數據傳入時刷新/擴展資源。

I hope to have shown you something useful. You can get started building your Google Cloud own solution with your own data using GCP’s free tier account.

我希望向您展示一些有用的東西。 您可以使用GCP的免費套餐帳戶開始使用自己的數據構建自己的Google Cloud解決方案。

Thanks very much for reading!

非常感謝您的閱讀！

Access my free Data Science resource checklist here

在此處 訪問我的免費數據科學資源清單