Analyzing financial time series with BigQuery

Here we will run a time-series analysis on a public dataset called gbpusd which is a curated time series made publicly available with historical data on the exchange rate between the British Pound (GBP)  and the US Dollar (USD).

1. Make a dataset called timeseries using following command on Cloud Shell

bq mk timeseries

2. Load gbpusd dataset with following command:

bq load timeseries.gbpusd_0114 gs://solutions-public-assets/time-series-master/GBPUSD_2014_01.csv /                            venue:STRING,
currencies:STRING,
time:TIMESTAMP,
bid:FLOAT,
ask:FLOAT

3. Now, run following query:

SELECT FORMAT_UTC_USEC(time) AS time, venue, currencies, time, bid, ask
FROM  timeseries.gbpusd_0114
ORDER BY time ASC
LIMIT   1000;

4. Download the query results as CSV file
5. Open Google Sheets. Import the downloaded CSV file by clicking on File | Import
6. When prompted, select Replace Current Sheet

 

7. Select all of the cells and click on Insert | Chart and choose your appropriate chart...