Last week I had the misfortune of having my ID scanned. My coworkers were having a happy hour after work and we decided to meet up at True North Tavern in North Park here in San Diego. I didn't know the bouncer was going to scan it, in fact, he didn't tell me what he was going to do. I didn't realize what he had done until my ID picture popped up on the black podium-like scanner machine's screen. I asked him, "are you guys keeping my driver's license data?", to which he responded, "read all about it over there" as he points to a laminated piece of paper describing the scanner, the need to scan IDs, etc. 

What I don't understand is the lack of communication prior to scanning my ID. Where was the consent that I gave? It surely wasn't verbal, so did I "give" consent merely by wanting to enter their premises? I don't think so. Sure, I wanted to go in, but by no means do I want my ID data anywhere for these people to have. Had they asked me for permission to scan my ID, I would have turned around and gone elsewhere. 

So, why does this matter? Why do I care about having my ID scanned? Well, for starters, let's talk about all the data breaches that have happened in just the last year. I'm talking about Target, Home Depot, Anthem, and (most shocking) the OPM data breach. These types of data breaches have become more common and I wouldn't be surprised if these ID scanners don't offer high-level encryption or security. 

Aside from the potential flaw in data safekeeping, I also care about my privacy. I don't want these ID scanning entities to "data mine" my ID information and potentially connect it to the credit card I used that night. Besides that potential risk, the following are a few ways that a decent analyst can use data gathered from ID scanners: 

• Link your ID to your credit card used at the bar and figure out how much money you spent
  • Offer promotions (VIP) to people who spend a certain amount.
• Log the time you entered the bar and keep a log of the frequency of your visits.
  • This could then be used to see what days you typically frequent the bar and could maybe be used to figure out when clientele is low/high to determine staffing needs, for marketing campaigns to draw more customers in on low-visit days, etc.
•  Log your date of birth
  • Send you marketing material on your birthday 
  • Create an age breakdown of clientele. 
• Log your address
  • Send you marketing material
  • Match customers to zip codes to figure out what zip codes frequent the bar. Offer discounts to people from certain zip-codes.  
• Log your picture ID
  • Create an internal "face" book with clientele who have visited the bar

Aside from the above, bar owners claim these ID scanners are good because they keep banned people out and to ensure there are no under-age drinkers or people who double scan IDs (when you let someone in front of you borrow your ID). Fair enough.

What scares me is what the ID scanning companies might do with the data. Do they keep a copy of each bar's data? I would assume so, seeing that these machines alert bar owners to trouble-making patrons.There has to be some sort of data sharing. Also, how safe is their database? Would it be easy to hack into? 

Ultimately, who knows how the data is being used after your ID is logged or how secure it is, but I don't like it. I am aware that other companies have your data (Google, Facebook), but it's right there in the terms and conditions. Bouncers at these bars don't tell you until after they've taken your information (if ever), which doesn't seem right. For me, the cost of having my ID scanned and logged outweighs the benefit of visiting such bars. No thank you, so I'll take my business elsewhere.  

One of the things that I love about Stata is its ability to simplify my life. Seriously. For example, I sometimes work on big Excel spreadsheets that are full of links to other tabs and/or other worksheets. These links to other sheets can be tedious to update as a simple "drag and drop" approach doesn't translate to correct cells being referenced. While I could use Excel Macros to update these spreadsheets with correct cell references and links, I always opt for using Stata's handy putexcel function. 

Suppose I know that I'll be referencing a group of cells from different worksheets and that I need to perform certain calculations containing those references. In this example, I'll show you how to do just that.

In the code below, I use putexcel to update a file already created called Comparison.xlsx. I know that this final worksheet needs to be updated starting at row 3, so I set my local i = 3. From there it goes through all different files within my global directory that end in ".xlsm" using a loop. Note that I told Stata to modify my excel file. I could have chosen a replace or a sheet replace option, if I needed to, too. 

It then goes through the files in that directory using the local `m' which trims the ".xlsm" out of the filename in order to reference other Excel files (.xlsx) that have the same name and contain information I need. 

Notice all the cool things I did with putexcel: I referenced different files, different tabs, performed calculations on cell references (divided and summed across), made totals, and created a percent difference. Not only that, but I also formatted to a number displaying 2 decimal points format,  number format with the 'thousands' comma separator, and percent with no decimals, for example. 

Not shown here, but I also format colors, borders, and cell alignment using putexcel to create nice tables and presentable spreadsheets that others in the company use. 

Like magic, I run this do file and it updates all numbers I need, formats it with correct number formatting, makes it look pretty and all without needing to use Excel directly. I open up the excel file I edited, Comparison.xlsx, and I replace all "=" with "=" and automatically all links become live. 

I hope you enjoyed this post. For more putexcel examples--especially if you like mata, take a look at Will's post. 

clear all 
set more off
version 14.0

global rdir "c:\users\bchavez\desktop\"
global sdir "c:\users\bchavez\wp\summaries\"

putexcel set "\${rdir}Comparisons.xlsx", sheet("All_Years") modify

local i = 3 //Beginning of Excel file inputs 

local m = ""
local s = " "
local files: dir "${rdir}" files "*.xlsm"
foreach f of local files{
        local fs = subinstr("`f'", ".xlsm","",.)
        local m  "`m'`s'`fs'"
}

foreach scenario in `m'{
        putexcel B`i' = ("='\${sdir}[`scenario'.xlsx]Results'!L288") ///
                A`i' = ("`scenario'") ///
                C`i' = ("='${sdir}[`scenario'.xlsx]Results'!L287") ///
                D`i' = ("='${rdir}[`scenario'.xlsm]Pivot Tables'!L234") ///
                T`i' = ("='${sdir}[`scenario'.xlsx]Results'!O45/1000000") ///
                V`i' = ("=SUM(D`i':S`i')") ///
                X`i' = ("=SUM('${rdir}[`scenario'.xlsm]Pivot Tables'!C3224:K3224)") ///
                AA`i' = ("=SUM(Z`i',M`i')") ///
                AH`i' = ("=(Y`i'-X`i')/X`i'") ///
                B`i':C`i' = nformat(number_d2) ///
                D`i':F`i' = nformat(number_sep) ///
                T`i':U`i' = nformat(accountcur) ///
                AH`i':AP`i' = nformat(percent) 
        local ++i
}

tokenize `m'
local name = "`1'"

putexcel A2 = ("Scenarios:") ///
        B1 = ("Summary")   ///
        B2 = ("Cost Ratio") ///
        C2 = ("Implied Payback") ///
        D2 = ("Forecasted X") 
        

You know how when you have nothing in particular to talk about, you start talking about the weather? Well, let's talk about the weather. 
​
September was a really hot month. Well, at least in San Diego. The average temperature recorded at Miramar (KNKX) was 76.3 with a minimum of 66 and a maximum of 94. Compare this to a mean of 73.7, a minimum of 60 and a maximum of 90 in August. 

For this blog post I wanted to see how my steps looked like alongside the average hourly weather for September. Above, you'll see the hourly weather (in yellow) compared to my average hourly steps (in red)  alongside my coworker Will's hourly steps (in blue).

During the weekends in September I was more active as the day progressed and the weather got cooler. I took a few long runs or walks in August and September during Saturday/Sunday mornings which explains the spikes before noon. Overall, however, there was less activity during the hotter parts of the day in September than in August, which is to be expected. Nobody wants to be out running when it's hot and humid. Not a lot of surprising data, but it's nice to visualize it here.   

During weekdays, I'm at the office and so there's not a lot of step activity while I'm at work. Recall last month's post regarding my hourly step comparison with my coworker Will. Our steps during the work hours (8 AM - 6 PM) is correlated at 0.93 (our weekend steps were a little correlated at 0.85) in the month of September. A bit higher than last month's 0.89.

I'd be interested to see how correlated my steps are with other people who have a Fitbit at the office, but I have a suspicion it's probably the same as ours. Walks happen during the earlier part of the day, during lunch time, and then going back to the car after work.

Thankfully, I didn't suffer too much during last month's heat wave as I was at the office during those hours. Stay tuned for next month's monthly comparison. My plan is to see how Fitbit challenges, like the Weekend Warrior, look on an hourly basis. I hypothesize there are many lags as competitors are driven to beat each other's steps. 

​Day 0
​Thursday, October 8
Time: 7:10 PM

I'm at the gym, class is ending, and as I look over at the front desk area I see my Fitbit sitting on the counter. I think to myself, "don't forget to grab it before you leave." I talk to a  few people after class, go to the ladies locker room and upon my return I see that my Fitbit is no longer on the desk. I get a sinking feeling. My heart rate goes up, and my Fitbit isn't on my wrist to tell me so. I look around and I ask the front desk person if she's seen it. She tells me that she never saw it there and asks people around. The children playing around the front desk area admit that they played with it and then claim they set it down after picking it up. The gym staff and I look on the floor, around lockers and everywhere else trying to figure out where it might have gone. I open up the Fitbit app on my phone and I click on "sync now" trying to locate it as my first thought was that a kid probably stashed it away in some remote part of the gym while playing with it and was too embarrassed to confess after the fact. No luck. I do notice, however, that there are three cameras pointing at the front desk, so I ask them if they can see who might have grabbed it. They tell me they'll look into it. I leave sad and instantly feel naked without my Fitbit.

​Time: 9:50 PM
I log into the Fitbit app on my phone (old habits die hard) and I see that my Fitbit synced at 8:51 PM. WHAT? HOW?! I go to Fitbit's website and it says "If it synced recently, that means it is within 15-20 feet of a computer with the Fitbit Connect software installed and a dongle plugged in. " This assures me that someone at the gym walked out of it with it. I immediately email Fitbit's support account and let them know what happened. After finding out that my Fitbit is syncing, I decided to find out more about my thief. So I keep checking the app because, clearly, this person lives in a house where there's a Fitbit dongle. 

Day 1
Friday, October 9 
A full 24 hours have passed. The cameras at the gym still hadn't been reviewed, so I check on the Fitbit and I see its "Last Sync" change to recent times, so I decide to add some alarms to annoy the thief. Did you know that you can only have up to 8 alarms on your Fitbit? I sure didn't. Thanks, thief. I added a few alarms set to go off before dawn to teach the thief a lesson: don't mess with me or my Fitbit...

​Sure enough, the 2:36 AM alarm made this person get up as they seemed to have taken a few steps. It looks like the other alarms went off, too, and disturbed this person's sleep. Mission accomplished!

I kept checking the app and what do you know? More heart rate data, but very few steps. This person seemed to be wearing it intermittently.  

Day 2
Saturday, October 9
I get a phone call from the gym telling me that they've found a Fitbit and that might belong to me. I go check it out, and lo and behold, there it is. It works fine and it synced right away. The only thing is that it smells of Chanel No. 5 (yuck) and it irks me that someone else was wearing it. The gym had told me that they couldn't check the camera feed without the I.T. guy, who apparently doesn't live in the area, yet had assured me that if they couldn't find it by Sunday they'd buy me a new one. Well, that's nice. At the end of the day, I did get my Fitbit back, but part of me wants a new one after seeing all this activity. Wouldn't you? Oh well. Little Fitbit of mine, where you were, we'll never know.