Google Sheets Intermediate Project: Comparing the Best Daily Currency Exchange Rates Between Two Agencies – XRP/USD Example Part 1

Google Sheets: FILTER, ARRAYFORMULA, UNIQUE, WEEKDAY, LEFT, MIN, MAX, IFERROR, Conditional formatting, workflow.

In this tutorial, we are going to tackle some Intermediate level Google Sheets concepts including using functions like:

• FLITER
• ARRAYFORMULA
• MIN and MAX
• IFERROR
• SPARKLINE
• UNIQUE
• WEEKDAY
• LEFT

We are then going to make our Google Sheet more presentable by doing some neat tricks with Conditional Formatting.

However, probably the most important lesson is to consider throughout the whole process is the workflow of this project. I will try and get you into my headspace and talk about:

• How I came to do things in a certain way.
• What I tested before applying to my Google Sheet.
• The order I did things to create the test.

When I am learning something, I find the most benefit in learning by following a project. As you follow along you don’t have to read everything. Actually, I encourage you to get the basic idea of the stage before giving it a try. Then come back to the tutorial for troubleshooting.

I hope you enjoy the following project…

Contents

• The Project
• Getting Started
• Date Data
• Getting the USD-XRP Exchange Rate For Each Day
• Percentage Difference Between GateHub and Bitstamp
• Conclusion

Back to Top

The Project

In this project, we are going to compare two currency exchange agency’s exchange rates daily. We want to see two things:

1. Which Exchange agency has a higher or lower exchange rate?
2. The average exchange rate between the two exchange Agencies.

For this example, we are going to look at the how much the cryptocurrency Ripple XRP is valued at in USD in the two exchanges Bitstamp and Gatehub.

We generated live USD/XRP data for these two exchanges for over two months now. You can see how we did it here if you want to dive into Google Apps Script:

Google Apps Script – URL Fetch JSON data from an API and add it to a Google Sheet – Ripple API example

Back to Top

Getting Started

To get started with this project you need a copy of the raw data. Select this link to a View Only copy of the Google Sheet:

XRP Google Sheet

Make an editable copy for yourself by going to File > Make a copy.

The Raw Data

On the first tab Exchange Volume is the raw data.

We will be grabbing this data, and in another tab, transform it into something much more readable.

The Final Result

As you can see in the image above, in our Daily Exchange Rate tab we have brought in the Date and the two agency rates for Gatehub and Bitstamp for USD-XRP. We’ve then calculated their average (Col E). Displayed the difference between the two as a percentage (Col F) before displaying which agency has the highest exchange for the day (Col G).

Columns B to G are essentially comparing the results from the two exchange gateways, Gatehub and Bitstamp.

In columns H-J we look at the total day change of the average of the two gateways to see if the exchange rate has gone up from the previous day or down.

Lastly, we add a 7-day Sparkline chart of the average USD-XRP exchange rate over a 7 day period.

To make everything more presentable, we’ve used some custom conditional formatting rules to make it all easier to see trends at a glance.

First step

If you are following along, go ahead and create a new Google Sheet tab. I’m gonna call mine, Tutorial (Creative, I know).
Back to Top

Date Data

You might have noticed that there is no visible Column A in the Daily Exchange Rate tab. Rather the sheet starts with column B, which is represented by a simple date in year-month-day format.

Date TIME STamp

In column A we are going to bring across all the dates from the raw data in the Exchange Volume Google Sheet tab. You can see in the raw data that the date is represented as a date time stamp in Zulu time (Greenwich Meantime).

To do this we are going to use the UNIQUE formula to get all the unique dates.

The UNIQUE formula takes one parameter which will be an array of columns or rows. When applied, it removes any duplicates values while maintaining the order where the first item occurred in the list.

In our example, we have two duplicate dates for every day. In your newly created Tutorial tab in cell A3 type in the following:

You can see that we didn’t select the first row ( The header) favouring A2 and we left the row unlimited by leaving out a row value. Of course, you can just select the range in the Exchange Volume tab if you don’t want to type it in. That’s what we did.

Because the UNIQUE function is constantly looking at the Exchange Volume range without limit, it will automatically update if more date-time stamps are added to that column.

In cell A1, give your column the header  DTS. These select both A1 and A2 and merge the cells. Respace your column widths so that it doesn’t overlap into column B too.

Your Tutorial Google Sheet should look like this now. Note the formula in cell A3 I have selected.

Take a look at all the other consecutive rows in column A. You won’t see any formulas there because we use a formula that displays an array of data. This is common for these types of formulas.

Just The Date

The date timestamp is not the most attractive thing to see in our presentation of our data. Also, the time really isn’t that necessary for the analysis of the data. All we really want is the date.

To do this we are going to put the date in Column B. We can get the first characters in column A by using the LEFT function.

LEFT grabs all the characters from the left up to an assigned number of values. The function takes two parameters:

1. The string or cell the string is in.
2. How many characters or spaces from the left you want to collect.

In our example, we will grab the date timestamp data from column A. We only want the date and not the time. So that’s 10 characters including the dashes:

In cell B3 we might want to do something like this:

Your result would be: 2019-05-13

You then may be inclined to double click or drag all those formulas down the row. However, we can do something a little more manageable here that will allow us to put just one formula in cell B3.

The ARRAYFORMULA is a powerhouse! You can use this function along with other functions you would normally use on individual cell items. The ARRAYFORMULA allows us to input whole arrays of columns or rows of data and assign formulas to it.

Let’s take a look at our current example (Don’t worry we will be using ARRAYFORMULAS a fair bit in this tutorial).

Take a look at the LEFT function first. You can now see that we have an array instead of a single cell value going from the third row of A on to eternity. Because the LEFT function is inside the ARRAYFORMULA we are able to pull this little bit of magic off.

Pretty cool, huh?

Go ahead and add this to B3 of your Tutorial sheet. Add the header just like before, merging the two rows.

Your Tutorial Google Sheet should now look like this:

Finally, right-click on column A and select Hide column. We don’t need to display that.

Highlight Every Sunday

Our date column is almost done, but it would be kinda cool to be able to see where the week starts at a glance. Conditional formatting custom formulas to the rescue here.

If you are doing this intermediate level Google Sheets tutorial you would probably be familiar with a bit of conditional formatting by now. However, you might not be too familiar with how to use the Custom Formula option. We are really going to make use of this in our tutorial, so strap in.

First, select all of column B. Then in the menu select Format > Conditional formatting…

When the sidebar pops ups, make sure your range starts at B3 and goes to B1000 or more.

Under  Formula Rules, select Custom formula is… . A text box will appear underneath for you to add your custom formula.

The most important thing to remember when creating a custom formula in conditional formatting is that it is always based on an if statement. That’s why you see the little format cells if… in the instructions. The second most important thing to remember is that your formulas will represent an example of the first – top-left – cell you want to apply your conditional formatting to.

Take a look at our example. We will apply our conditional formatting to our range B3:B1000. This means that our first custom formula will be in cell B3. 

We can find out if a specific date is on a Sunday by using the WEEKDAY function. WEEKDAY takes a date as its first parameter and also has an optional parameter, but we won’t be using that.

Don’t get confused by the name of the function. WEEKDAY will help you find every day of the week based on the date.

When WEEKDAY is executed, it returns a number. If no optional parameter is in place then WEEKDAY will default to a Sunday equaling 1, Monday 2, etc.

So all we have to do in our custom formula is add this:

This states that if the date in B3 lands on a Sunday then WEEKDAY will produce 1. If WEEKDAY returns 1, then apply the conditional formatting.

Finally, let’s make our conditional formatting fill the cell a light grey.

This is how your sidebar should look:

Once you have it all matching, click Done.
Back to Top

Getting the USD-XRP Exchange Rate For Each Day

Looking at our Exchange  Volume data we can see that there are two USD-XRP currency exchange gateways or agencies, Gatehub and Bitstamp.

We want to be able to clearly see each days exchange rate for both gateways. We also want to gather the average daily exchange rate for the two gateways.

Headers

We’ll get our headers sorted out first for this segment. These will be important for formulas for us in a moment.

From C1 to  E1 we will add the main header:

USD-XRP Exchange Rate

Merge that across to cover all 3 cells.

On the next row we will add our gateways and the average title:

• C2: Gatehub
• D2: Bitstamp
• E2: Average

We are going to reference these two gateways later so it is probably a good idea to colour-code them for easy visual reference. I made Gatehub green and Bitstamp purple.

Bold all your headers you created so far and centre them.

Your Tutorial sheet should look like this:

Grabbing the Gateway Exchange Rate for Each Day

To display the exchange rate for each gateway, we will need to filter all the data down so that we are left with the exchange rate for a particular gateway on a particular date. To accomplish this we are going to use the FILTER function.

The FILTER function removes any data you don’t want to be displayed based on one or more rules you create in the formula. The first parameter of the FILTER function is the range of the data you want to display. For us, this is the exchange rate in column F of the Exchange Volume Google Sheet.

The consecutive parameters are all conditions by which you want to filter out your data.

For our first condition, we want to make sure that we only have the exchange rate for the gateway in our current column. The exchange gateway name is available in column C of our Exchange Volume Google Sheet. We will go ahead and reference that entire column. We already have a name in our header on row 2, C or D. So we may as well reference that. We want to make this an Absolute Reference and lock in that position when we drag this formula down the page. We will do this by adding a dollar ($) sign to the row and column reference.

Your first condition should look like this:

For our second condition, we need to ensure that we filter out everything except the corresponding date in our current row of our Tutorial Google Sheet. We need to match the date-time stamp in our current row – the DTS column A we hid – to the range of date time stamps in our Exchange Volume Google Sheet. These can be found in column A.

Your second condition should look like this:

Putting the FILTER formula altogether, it should look like this for cell C4.

Give it a try in C3 and then have a go at changing the first condition so it works in D3 for the Bitstamp column.

It should look a little like this:

Making it work for every row

We will want to select these two formulas and double click the bottom right-hand corner or drag them down way past our current data range because we have live data added daily and we want our Google Sheet to be able to update automatically.

The problem is if we tried this with the current state of the formula we would get messy looking errors when there are blank spaces.

Let’s tidy that up by capturing our FILTER function inside an IFERROR function.

IFERROR takes two parameters. The first one is the function or functions in your cell. The second parameter is optional and allows you to add text or other functions should an error occur.

If your main set of function displays an error, then IFERROR will hide that error message and either display nothing if you did not use the optional second parameter or display whatever is in that optional second parameter.

I use IFERROR quite a lot to hide unnecessary error warnings when data has yet to be added.

Here is how it will look around our FILTER function in C3:

I’ll show you a different way to handle empty data in formulas in our Average column in a moment. Then it’s up to you to choose which one you like better.

Finally, grab your C3 and D3 formulas and drag them all the way down the bottom of your page to, say, row 1,000. Notice, no errors in the blank spaces.  Sweet!

At this stage, your Tutorial Google Sheet should look like this:

The Average Column

It’s probably pretty clear for you that we are now going to get the average exchange rate of the Gatehub and Bitstamp rates for each row.

For our Average column, we are going to be able to make use of that glorious ARRAYFORMULA function again. Remember, using the ARRAYFORMULA function will allow us to apply the average to every row all the way down our column.

So how is this going to work?

To help us understand the ARRAYFORMULA a bit more we are going to forego the AVERAGE function and go back to what we learnt at school. An average is the sum of all values divided by the number of values.

So for our first row, row 3. The average would look like this:

Remember, for our ARRAYFORMULA we can add in a range of values that will be calculated for each row. Using this function it would look like this:

Can you see the changes to the formula for calculating the average?

Now all the rows from C3 down to forever will have their averages calculated one row at a time.

Of course, we will still have that hiccup we had when we tried to use a formula on an empty space.

Instead of using IFERROR this time, let’s try and deal with it with an IF statement.

We can safely say that if there is no value in a row in column C then we should leave corresponding average row blank. Remember, we are still in the ARRAYFORMULA so we will need to provide an array for our IF statement to take. Let’s take a look at the whole formula now:

Go ahead and add that in cell E3 and watch the magic unfold. Don’t forget to scroll down past the data range and check that there are no errors.

A Note on Workflow for Building Complex Formulas

You are probably starting to see how we go about building more complex formulas in Google Sheets. Most of the time we will start from the inside formula and work our way out.

We usually test each step in the cell along the way. Sometimes we even test strings of formulas on multiple cells joining them all together with cell references before combining it into one.

Generally, the last step is putting in the IFERROR or hiding formulas on empty spaces stage. We do this last so we can better see any errors that pop up.

I think, getting your head around this type of workflow can really elevate your skill when building complex formulas for a Google Sheet.

Conditional Formatting High and Low Values

To help the reader quickly determine which gateway has the higher or lower value, we will use another custom formula in our conditional formatting.

This time around select C3:D1000. Note that I have selected both gateway data columns here.

When we provide our custom formula sample we will first reference C3. The conditional formatting will read this sample formula and will apply it to both column C and D within the range of rows specified.

The lower exchange rate is always going to be less than the average of the two values. Likewise, the higher exchange rate is going to be higher than the average. This means we can use the average in our custom formulas to create our conditional formatting.

Our sample formula for the lower exchange rate for C3 would look like this:

And for the higher exchange  rate:

We have locked in the column of the average. With our absolute dollar ($) sign. If we did not do this, when the formula tried to evaluate a cell value in column D it would search in column F instead of our average column E.

Red seems like the most intuitive colour for a lower value and blue for a higher value. Your conditional formatting for the lower value should look like this:

Down the bottom of the conditional formatting sidebar click the Add another rule button.  You can now simply add the greater than symbol (>) and update the background colour.

Click Done. Your final result for this section should now look like this:

Back to Top

Percentage Difference Between GateHub and Bitstamp

Another important piece of data we can add is an indicator of how much the two exchange gateways diverge from one and another. We can get the percentage of their difference by subtracting the highest value by the lowest value and then dividing that by the average:

MAX and MIN to the rescue

To get the highest and lowest values we can use the function MAX and MIN respectively. These functions evaluate a range of data in their first parameter and return the largest or smallest value.

This means that if we subtract our MAX value from our MIN value we will always have a positive number.

To get our first row of results we would apply the formula like this:

Give it a try.

MANAGING Empty Cells

I like using the IFERROR function to hide cells – it just looks cleaner to me.

Have a crack at adding it yourself.

…

How did you go? Your formula should look a little like this now:

Finally, drag that formula down the column all the way to the bottom of the page.

Header

Lastly, add your header after merging cells F1:F2:

% Difference Between GateHub and Bitstamp

You’ll probably need to go to Format > Text wrapping > Wrap and then drag down the rows a bit to make the header fit better.

By this stage, your Tutorial Google Sheet should look like this:

That percentage column stills looks a bit crowded and confusing for the reader. Let’s select that column and apply a percentage to it.

Much better.
Back to Top

Conclusion of Part 1

Nice! We made it halfway!

So far we have explored working with live data. We have used ARRAYFORMULA  and LEFT to get a nice neat date. Then we have used the FILTER function to get each exchange rate for each day before applying a daily average. Lastly, we have calculated the difference percentage of the divergence between the two exchange gateways.

Also, we discovered two methods to get rid of unwanted errors when applying formulas to empty cells using IFERROR and IF statements.

To top it all off, we have got in touch with our esthetic side and applied some neat conditional formating to the date so we can clearly see each week. We also added some conditional formatting to the exchange rates so we can see at a glance which gateway has the highest and lowest exchange value for each day.

What’s next?

In Part 2 of this tutorial Project, we will:

• Create a column to immediately identify by name which gateway has the highest exchange rate for the day.
• Get the day change of the average of the two gateways.
• Add a 7-day Sparkline graph so we quickly see the performance of the exchange over 7 days.

Here is a sneak preview of what to expect:

Go on to part 2 HERE!

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.