12 Replies Latest reply: Oct 22, 2016 2:18 PM by alwweb RSS

Point Spreadsheet

wally3433 Platinum 2 Reviews
Currently Being Moderated

I have so many missing points at this point, I have to start using a spreadsheet to start keeping track of everything.

 

I know there are alot of you that have spreadsheets already.  I know it's a simple spreadsheet, but can anyone share at least the column headings that you use?  Just curious at the level of detail that each of you go to.

 

Thanks,

(For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

  • Re: Point Spreadsheet
    erc Platinum
    Currently Being Moderated

    Great post. pingreeman this is right in your wheelhouse, can you lend us a hand?

     

    Not only is the accuracy of Marriott posting often at issue, even if they were 100% accurate (which it would be hard to convince me they are) the different posting techniques (they often spread points over two months) make it difficult for even the most vigilant to follow. After awhile (coupled with individual properties not always posting) it just wears you down and The Man wins .

    (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

    • Re: Point Spreadsheet
      eb5147 Alumni Steward Platinum 7 Reviews
      Currently Being Moderated

      When my father was a boy, the town exacted a penny tax on the 5 cent theater tickets.  My father went to the picture show the first day they did it and only had a nickle, so he went to the ticket counter and asked for 5 pennies, which he then took to the next window and asked for it to be changed.  He did this back and forth when they finally asked him what he was doing.  He told them that one of them would eventually be wrong and it wasn't going to be him.  I think they probably gave him the extra penny.  This was in a small TX town during the '30's since he was born in '24.

       

      Later years, he sold snacks at the same theater for a commission, I guess.  He put a nickle into one of the bags of popcorn, which someone yelled about when they found it.  Everyone started buying popcorn in case he'd dropped another nickle into the popcorn, so he would periodically drop another nickle; pretty much like the diamond salting at the diamond mine in AR.

       

        Diamond Mines - Crater of Diamonds State Park

       

      The 100% accurate statement just reminded me of it and I thought it appropriate.  If it's not, I can delete it. 

      (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

    • Re: Point Spreadsheet
      pingreeman Platinum 1 Reviews
      Currently Being Moderated

      My very first entry is from 10/19/2008 back when the Account Activity could easily be copied/pasted into Excel and all the columns would line up nicely. In today's account view, it does not copy/paste as easily (columns appear as rows like trying to do a PDF copy/paste). With a few minutes of reformatting and copy/paste using "Paste Special - Transpose", I can clean up my activity in a few short minutes. Point is, I am still using columns from 2008.

       

      Having said that, the look of my Excel spreadsheet has the following column headers:

      Date from - used for hotel stay checkin date as well as any non stay points, i.e., the date the points were deposited

      Date to - used for hotel stay checkout date; otherwise left blank

      Description - copy/paste of whatever is on my account description

      Earnings Breakdown - this is a description I add to each row - see the limited choices I use below

      Total Earnings - this is the number of points deposited or withdrawn and are signed numbers, i.e., points used are negative

      # of Days - from the older account view, this is the number of days of the stay; I now manually calculate this value

      Year End - I use this column to count my number of nights for the year using a =SUM(xx:zz) formula where xx is start of year cell and zz is end of year cell

      Notes - I use this column for comments; example: "LTD nights validated by MR CSR"


      Within the Earnings Breakdown section, I categorize each row into one of these (so I can build a pivot table on the point-types):

      Cashed - points used for gift cards, purchases, free nights, etc.

      Megabonus - points earned through megabonus program

      Misc bonus - any points not earned through stays, Visa bonus, etc.; most of these points are "compensatory" where a manager bonuses me points for an issue during my stay or from taking surveys

      Reward - this is used to log non-points items such as free night promos or annual Visa Hotel Stay

      Venue - points earned from a stay and includes base and bonus and arrival points

      Visa - Air/Car/Dine - Visa points earned under this category

      Visa - Marriott Spend - Visa points earned under this category

      Visa - non-Marriott - Visa points earned under this category

      (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

      • Re: Point Spreadsheet
        alwweb Platinum 4 Reviews
        Currently Being Moderated

        Do you have a nice macro or something to do the clean-up?  Mine comes in with tons of extras that aren't consistent (like last qualifying stay date in many rows).    Clean-up is going to take me forever, but I would really like to do a better job of tracking all of this since there seem to be a ton of inconsistencies.

         

        Thanks!

        (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

        • Re: Point Spreadsheet
          pingreeman Platinum 1 Reviews
          Currently Being Moderated

          alwweb - I do not have a macro, but what I do have is one sheet within Excel where I paste-SPECIAL the rows from my activity into the THIRD row of my spreadsheet - FIRST row as first row has formulas, SECOND row has my column headers). Within this tab, I delete "Actions Column" words such as "Ordered" or "Cancelled" to remove these occasional rows and also delete any blank rows (I filter on the items and then do delete-row). Once the preliminary "clean up" is done, it then becomes a matter of formulas to do the rest of the cleanup. (When my cleanup is done, every third row has the date of the points earned/used).

           

          I then copy-down formulas from the FIRST row on the spreadsheet. One of the formulas determines if the cell in column A is a date and if a date, puts in the date in column B, otherwise a blank: {=IFERROR(IF(WEEKNUM(A3,1)>0,A3,""),"")}

           

          Once I have date rows identified, then it is a matter of additional formulas to populate the additional columns to line up in rows (Under the headings of "Date", "Reward/Bonus", and "Points") as shown below.

           

          Once all formulas are copied down to the last cell, I then take a copy to my "Tracking" sheet of all points and do a PASTE-SPECIAL-VALUES so the actual calculated cells are on my tracking sheet. I then delete any blank rows from the tracking sheet and have all my data in a nice format where I can analyze it, do pivot tables, etc.

           

          NOTE - My current MR activiy has no paid stays and as such, you may have more filtering/deleting than did I in preparing for the formulas. But the concept is the same - - if you have more line items to populate columns, you may need some additional formulas to identify column values.

           

          FORMULA ROW=IFERROR(IF(WEEKNUM(A1,1)>0,A1,""),"")=IF(B1<>"",A2,"")=IF(B1<>"",IFERROR(VALUE(LEFT(A3, SEARCH("points",A3,1)-1)),""),"")
          PasteDateReward/BonusPoints
          9/30/20169/30/2016PREMIER VISA ANNIV CAT 1-5 HOTELS0
          PREMIER VISA ANNIV CAT 1-5 HOTELS
          0 points
          10/1/201610/1/2016PREMIER VISA - NON-MARRIOTT SPND3623
          PREMIER VISA - NON-MARRIOTT SPND
          3,623 points
          10/1/201610/1/2016PREMIER VISA - AIR/CAR/DINE SPND137
          PREMIER VISA - AIR/CAR/DINE SPND
          137 points

          (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

      • Re: Point Spreadsheet
        ssindc Platinum 42 Reviews
        Currently Being Moderated

        erc beat me to the punch on this....  when you're talking spreadsheets, you're talking pingreeman!

        (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

  • Re: Point Spreadsheet
    bejacob Platinum 38 Reviews
    Currently Being Moderated

    wally3433, I guess it depends on the level of detail you want. Here's what I use.

     

    For hotel points I list Base, Elite, Extra (i.e. Plat arrival gift) and then any Bonus specific to the property.

    I also have a columm for my Visa points (I just lump all those together rather than keep track of each category such as 2x at restaurants, 5x at Marriott etc.). Having a place for any promo/other bonus points is wise as well, though you could include those under the Bonus category mentioned above.

     

    For each property it should be easy to figure the base, elite and extra points you should earn.

    (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

  • Re: Point Spreadsheet
    zukracer Alumni Steward Platinum 4 Reviews
    Currently Being Moderated

    I used to keep track of it all but then it turned into a full time sorta fun.  I tend to just watch the points post then don't worry too much about them.  I seem to be burning them faster than earning so I figure at some point, sooner than later it seems, I'll be able to easily identify them.  They'll be the only ones in the kitty

     

    I like the way bejacob has it broken down though.   It can be a science for sure though.  I had a co-worker who's wife kept track of everything for him.  She would keep up with all promos, bonus deals, etc and actually tell him how to book his travel to maximize it all.  She was a stay at home mom, so this was her way to contribute to their family vacation planning.  Worked out pretty well, they could take a trip a quarter to Korea first/business class with the family of 4 so she must have been doing something right

    (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

  • Re: Point Spreadsheet
    softbal020 Platinum
    Currently Being Moderated

    Do you find the visa points are ever off?

    (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

    • Re: Point Spreadsheet
      YogiB Platinum
      Currently Being Moderated

      Often the visa points are off.

       

      Sometimes the "Merchant category" is wrong. The Delta Hotel Ottawa City Centre is category "Golden Tulip Hotels" (small hotel chain subsidiary in Europe). Or when dining at hotel properties, I only get 2pts/$ if the restaurant has its own billing system and doesn't pop up as a hotel merchant (e.g. the restaurant at the Marriott Courtyard Downtown Montreal pops up under its own name Darlington and, consequently, doesn't get 5pts/$).

       

      Chase usually doesn't want to lift a finger and just points towards MR CS to fix it.

      (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

    • Re: Point Spreadsheet
      alwweb Platinum 4 Reviews
      Currently Being Moderated

      I have had things happen like when I cancelled a reservation and certificate in plenty of time, but the points were never adjusted back into my  listing.  A quick email fixed it though.

      (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

  • Re: Point Spreadsheet
    softbal020 Platinum
    Currently Being Moderated

    What are the most common missing points?  I've been staying at a property with 2,000 bonus points per stay and its applied by the hotel so those I have been tracking bc I notice they are never applied correctly.

    (For each location tag, you will be guided through a 3-step process to add (1) a city and a state or a city and a country, (2) a Marriott brand, and (3) a Marriott hotel.)

More Like This

  • Retrieving data ...