0

I am trying to analyze the relationship between Bitcoin Market Price and Google Search Trends for the word "Bitcoin." For the former, I am using the chart found at blockchain.info downloaded in .CSV format; for the latter, I am using Google Trends data (search "Bitcoin" in the Google Trends page), also downloaded in .CSV format. I am planning to graph them against each other in Excel, do some math on them, etc... The issue is, Google Trends data is logged weekly, while Blockchain.info data is logged daily. This means the resulting rows don't line up after the first data point:

Excel Workbook Screenshot

Is there a quick shortcut I can use to spread out the Google Trends data, so that the dates in each row match (and so that there are blank A and B rows where data does not exist for a given date in the blockchain data)? Any and all help greatly appreciated!

NOTE: I realize analyses of these data have been performed before; I want to do them myself for self-educational purposes.

teylyn
  • 34,374
  • 4
  • 53
  • 73
Matt Billman
  • 472
  • 5
  • 19
  • how are you filling the blanks when spreading out the google trends weekly data to daily values? shouldn't you do the reverse instead, collapsing the daily blockchain data into weekly values? – PA. Jun 21 '16 at 06:38
  • It would be polite to get back to the people who are trying to solve your problem for free. – teylyn Jun 23 '16 at 08:13

3 Answers3

1

Weekly Google trends aligned with daily market price

UPDATE: teylyn's 2nd answer is more efficient than this method, and also fulfills what I think you're looking for

The graph will end up looking like this: Chart - rough, though you can change the names of the trend lines and/or juxtapose a bar graph with a line graph too (see below)

1) Line up all your data into three columns, as shown below: Excel data snapshot

2) Create a pivot table.
Select the data.
Go to Insert > PivotTable

3) Manipulate pivot table
Row Labels: Date
Column Labels: Google Trends, Bitcoin Price

Make sure the values are using the Sum. (If Count or another option is selected, click on the dropdown for each value, then click Value Field Settings, then select "Sum")

Again, this will only work if the date stamps match. Please let me know otherwise.

3) Create a line chart
Click anywhere in the pivotTable, and create an Excel line chart.
Right click on the chart, and click "Select Data".
Click "Hidden and Empty Cells"
Select "Connect Data points with line"
Hit Ok through both prompts....and YAYYYYY

Extra formatting tips for visualizing this data set:
If the scale of the trend lines is wacky, right click on one of the trend lines, and go to:

Format Data Series > Series Options > Secondary Axis

If you want one of the series to be a bar graph (to juxtapose a bar graph with a line graph), select one of the trend lines, go to Design > Change Chart Type

Bryan Yee
  • 11
  • 1
  • 3
0

Use an XY Scatter chart with the time values on the X axis or a line chart with a time axis on the X axis. The time values will be plotted according to their value.

enter image description here

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • That doesn't solve my issue - I want the final graph to have Trends on the X axis and Price on the Y axis. Lining up the data based on time is to ensure that a given Trend datum is graphed against it's contemporary Price datum. – Matt Billman Jun 21 '16 at 09:01
  • On a XY Scatter chart or a line chart with a date/time x axis, plot two data points from different series where both have the first of January as the X value. They will line up perfectly on the first of January. What about that don't you want? You don't need to space the source data to line up the dates in a chart. I may totally misunderstand your question. Please mock up the desired result and edit your question to show that. – teylyn Jun 21 '16 at 10:25
0

First time I ever added another answer. In cell E2 add a formula:

=iferror(vlookup(C2,$A$2:$B$10000,2,false),na())

Adjust to your ranges and copy down.

This will look up the daily date in the weekly column and return the value for the matched date from column B. Replace NA() with whatever suits your purpose best. Charts will ignore NA() as values.

teylyn
  • 34,374
  • 4
  • 53
  • 73