Keeping Uber honest – Using Excel to keep track of earnings, points and so on

So i’ve had a bit of a fustrating issue lately, where my account is caught by a bug causing my Momentum card not to have correct status or points.

As part of digging into this, I was looking into how to easily verify this. None of the 3rd party solutions I could find had any capacity for this kind of audit – so had to build my own, which I ended up doing in Excel.

This post will walk you through how to do it, and covers the small bits of setup and learning to get you started.

Once you’ve set this up once, it almost becomes set and forget. You just need to add your data each week, and Excel does the rest.

If you already have intermediate or greater level expereicnce using Microsoft Excel, this should take no more than 5-10 minutes to get going. Beginners should put aside an extra 10-20 minutes for some reading and concentration.

After that, you should be able to add new data and get your analysis after a payout with no more than 2min of effort (and that includes the time to login to the partner website, and download the file).

 

In this post:

• What this does
• What this does not do
• What it looks like
• Before you begin
• Import the macro
• Update the points grid
• Clean up your single CSV file (and this is the start point each time you have new data to import after the initial setup steps)
• Copy over the clean data
• Refresh the pivot table, and see the magic
• Questions? Comments? Expressions of gratitude?

 

What this does (use cases)

With this spreadsheet, you can:

1. Keep track of all your partner data by:
   • Importing your statements into a single sheet within an Excel workbook, and
   • Using Macros, cleans up the data for consistent recording and data analysis
2. Keep track of your Uber Pro/Momentum status by:
   • Updating the Points Grid to match your market, and
   • Using VLOOKUP and MATCH formulas, correctly calculate the points that should have been earned for this activity.
3. Analyse and verify your Uber status and payments by:
   • Using PivotTables to quickly present and view your data, and
   • Perform mathmatical checks to cross-check payment amounts and other information.

 

What this does not do (things you’ll need to be aware of)

Because Uber Partner data differs all around the world, and even some programs can differ, this spreadsheet template and method covered in this article does not cover:

• Removing currencies other than $A:
  If currencies in your exports are expressed in something other than $A, then modify the macro code accordingly for its find and replace functions.
• Handling columns beyond a specific set:
  The spreadsheet template and macros have been designed to handle a specific column header set from the Uber exports (Specficially: Driver name, Phone number, Email, Date/Time, Trip ID, Type, Fare, Service Fee, Tip, Quest Promotion, and Total). Again, if some of your exports have different columns, learn more about Excel, Macros, and Pivot Tables for how to ensure a consistent set of clean data and reporting.
• Automatically adding the program points grid for your market:
  Again, this isn’t something Uber publishes as a data table or via API to be easily accessed. As noted in the Before you begin section, you’ll need to locate the points grid on Uber’s website, and add this information into the right place on the workbook.
• Handling quarterly qualifying periods:
  Where partner programs (such as Uber Pro Driver) use a quarterly period for qualification, you can learn more about Pivot Tables and Grouping to render the data that way.
• Tracking and reporting cancellation rates:
  I’m not aware of any data export available to partners which allows you to get access to information on your cancellation rate. And as I’m only a delivery partner and not a driver partner, I don’t know how you currently get access to this information. You could build a column to track this yourself, and learn more about Pivot Tables to present this data.
• Tracking and reporting feedback:
  Again, as there’s no way I know of to get your ride-by-ride feedback data, reporting and analysing this in the spreadsheet isn’t already baked in. Again, you could build a column to track this yourself, and learn more about Pivot Tables to present this data.

 

What it looks like

Here’s some examples of the data analysis performed by the Excel workbook:

Example of analysis of my earnings (parts of the data hidden because not all of the information is appropriate to share).

Example of the Momentum points earned by Month.

 

Before you begin

1. Have access to a desktop computer, with Microsoft Office installed.
2. Get a copy of the points grid for your market and reward program (Momentum or Pro) to your computer: For my market – the Momentum points grid is at http://t.uber.com/AUSMomentumPoints; or the Uber Pro grid is here.
3. Download your recent driver statements into a Single folder on your computer; then consolidate all the CSV’s into a single CSV file (Instructions for Windows, Mac).
   (Depending on how many files you have to download, this might take a while, unless you can cook up a script to download them all)
4. Download my template file. (Download link)
5. Enable the Developer tab in Excel (Windows, Mac).

Import the macro

One of the key steps in making this process seamless is the macro that cleans up your weekly CSV file for future data imports.

1. In Excel, go to the Developer tab.
2. Follow these steps to create a Personal Macro Workbook.
3. Still on the Developer tab, press Visual Basic.
4. In the Project window, expand VBAProject (PERSONAL.XLSB) > Modules.
5. Right-click on Module1, and select View Code.
6. Copy the text below, and Paste the following into the window that now appears:

   Sub FormatUberCSVFile()
   '
   ' FormatUberCSVFile Macro
   ' Clean up an Uber Driver Partner CSV export for import into a master file
   '
   ' Keyboard Shortcut: Ctrl+Shift+U
   '
       Columns("D:D").Select
       Selection.TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
           TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
           Semicolon:=False, Comma:=True, Space:=True, Other:=False, FieldInfo:= _
           Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _
           TrailingMinusNumbers:=True
       Columns("G:K").Select
   
   'Remove currency symbols - Update what's inbetween the quotation marks for What to adjust the currency code to be removed from the file
       Selection.Replace What:="-A$", Replacement:="", LookAt:=xlPart, _
           SearchOrder:=xlByRows, MatchCase:=False, FormulaVersion:= _
           xlReplaceFormula2
       Selection.Replace What:="A$", Replacement:="", LookAt:=xlPart, _
           SearchOrder:=xlByRows, MatchCase:=False, FormulaVersion:= _
           xlReplaceFormula2
       
   'Format as currency
       Selection.Style = "Currency"
       
   'Select and sort data
       Range("A:P").Select
       ActiveWorkbook.Worksheets("statement_f0a85ede-d1df-b7ed-cc").Sort.SortFields. _
           Clear
       ActiveWorkbook.Worksheets("statement_f0a85ede-d1df-b7ed-cc").Sort.SortFields. _
           Add2 Key:=Range("M:M"), SortOn:=xlSortOnValues, Order:=xlAscending, _
           DataOption:=xlSortNormal
       ActiveWorkbook.Worksheets("statement_f0a85ede-d1df-b7ed-cc").Sort.SortFields. _
           Add2 Key:=Range("P:P"), SortOn:=xlSortOnValues, Order:=xlAscending, _
           DataOption:=xlSortNormal
       With ActiveWorkbook.Worksheets("statement_f0a85ede-d1df-b7ed-cc").Sort
           .SetRange Range("A:P")
           .Header = xlGuess
           .MatchCase = False
           .Orientation = xlTopToBottom
           .SortMethod = xlPinYin
           .Apply
       End With
   End Sub

7. If your currency is shown in a value other than A$ within the CSV exports, make sure you update the macro at the right places so it removes these values.
8. Press the Save button.
9. Close the Visual Basic window and return back to your Excel workbook.

 

Update the points grid

Now the macro is done, we need to add the points values by hour for your market. Otherwise, it can’t correctly calculate your Uber Pro/Momentum points earned for each activity in your market.

1. With the existing Excel Tracking file (Tracking.xslb), change to the Points Grid tab.
2. Using the

 

Clean up your single CSV file

Now, a reminder – each time you have new single data file to import, just open the file and start at Step 4.

1. Still in Excel, open the single CSV file you created earlier (Step 3 in before you begin).
2. Clean up the columns, so they go from this:
   

   Before cleaning up the data in the single file

   to this:

   

   After cleaning up the file

   Specifically

   1. Make sure that all the excessive header rows (Driver name, Phone number, etc) are removed from the file.
   2. Ensure that each data column has the correct data in it. Due to the way Uber creates its export CSV files, it just doesn’t ensure each column has the same data from file to file – so some files may have the Quest Promotion column if you had a quest that week, or the tip column will be removed if you received no tips that week.
3. Save this file, but keep it open.
4. Important: Run the macro to clean up the split up the Date column by pressing Control + Shift + U.
   
   Macro didn’t run?
   Using the ribbon, go to Developer, Press Macros, select the macro PERSONAL.XLSB!FormatUberCSVFile, then press the Run button.
5. Save the file.

 

Copy over the clean data

Congratulations, you’ve cleaned up all your data.

Copy it from the CSV file, and paste it into the Tracking tab within your existing Excel Tracking file (Tracking.xslb).

Refresh the pivot table, and see the magic

And now that the data is copied over:

1. Click on the Analysis – Earnings sheet at the bottom of the Excel workbook.
2. Click within the Pivot Table on the worksheet.
3. From the Ribbon, select Pivot Table Analyse > Refresh > Refresh All.

And that’s it, you have your data neatly presented and analyised, ready for you to do as you wish.

 

Questions? Comments? Expressions of gratitude?

Ask in the comments below. I’ll do my best to reply as and when I can.

And if this made things a little easier for you, don’t hesitate to send a couple of dollars my way as a token of appreciation.