For a little project I'm working on, I needed to understand how to generate QR codes. Being the Excel junkie that I am, I thought it would be easy to create a proof-of-concept entirely in Excel. As a challenge, I set a goal of doing this without using any Visual Basic or other scripting.

I will describe here the steps used to generate the code in Excel. Click here to download the Excel spreadsheet to follow along. You can also link to the Google Spreadsheet if you don't have Excel: http://bor.to/QRSheet

If you just want to generate the QR codes and not worry about the details of how it's done, just download the spreadsheet by clicking here and you can just use it standalone. Everything you need is in the tab "QR Code." Just enter text (up to 15 characters long) in the yellow box (Cell Y36) and the codes will be generated.

If you want to know how this all works, below is some explanation. To follow along, go to the "Generate QR" tab.

There are many sizes of QR codes, the smallest being a 21x21 pixel matrix known as Version 1 and the version that I have created in this spreadsheet.

To follow along, I will assume that you have not modified the original spreadsheet and "This is a test" is the string that we will encode into QR.

Step 1: There are several "Modes" you can use, but for the purposes of this spreadsheet we will use the "AlphaNumeric" mode. You can see that this is encoded with a 0010. For reasons I will explain in step 2, the spreadsheet will need to be modified if you want to use a different mode.

Step 2: We need to encode the overall length of the message. In a Version 1 AlphaNumeric QR code (21x21 pixels) there is only enough room for a maximum of 15 characters. Cell D4 has the text we want to encode and cell C7 is simply the length of the string displayed in 9-bit binary. "This is a test" contains 14 characters (including spaces). The number 14 in binary is 1110 and because we need to represent this as a 9-bit character, we put 0s to the left so that there are 9 characters. This is very easy to do in Excel with the DEC2BIN function. As I mentioned in Step 1, if you want to use another mode (Numeric, Binary or Japanese) you will need to modify Step 2 since they use a different number of bits. Numeric uses 10 bits, while Binary and Japanese use 8.

Step 3: We now need to encode the message itself. To do this, we break the message into sets of 2 characters (i.e. TH, IS,I, S, A, TE, ST). We also assign each letter a numeric value (as defined in the table I have placed in the Character Map sheet). For each pair of characters, we take the numeric value of the first character and multiply it by 45. Then we add that number to the numeric value of the second character. We then convert the result into an 11-bit binary string. If you are encoding an odd number of characters, then take the ASCII value of the final character and convert it into a 6-bit binary string. For the other modes (numeric, binary, and Japanese,) we would use a different method to encode the data. In Excel, I've done this by breaking out each character of the message in column G using the various text functions. Column H helps to tell us if it's an even or odd character (so we know if we need to multiply by 45) and column I has the numeric code that corresponds to each character using a lookup in the character map table. Column M helps us find which is the last row while Column N adds up the pair (or if there isn't a pair due to an odd number of characters, then just takes the last value). We need to convert the decimal number in column N to either an 11-bit or 6-bit binary number which I would ordinarily do with the DEC2BIN function. Unfortunately, Excel cannot do DEC2BIN on decimal numbers larger than 511. So, in Column O you can see the workaround that I had to use by breaking it down into small chunks and then putting it back together.

Step 4: Here we simply combine the strings from steps 1, 2 and 3. There is an IF/THEN function in there to handle the cases where the message we want to encode (e.g. "This is a test") has an even or odd number of characters.

Step 5: We now need to make sure that we have the correct length of string. QR codes have in-built error correction and there are different levels you can use. We will use Level Q. For this we need to make sure that we create a string with a total length of 104 bits. In Step 5 we add up to four 0s to lengthen the string. In our case, the length of the string in Step 4 was 90 characters, so we add four 0s to the end. If the length of the string in Step 4 was 102 characters, then we would only add two 0s to the end. This is done in Excel with a series of IF/THEN functions.

Step 6: If by the end of step 5 we still don't have the correct length (104 bits) then we need to continue padding it out. The way to do this is to break the string from Step 5 into 8-bit words. We will need 13 of these 8-bit words (8x13=104) by the time we're done. Column AB has the string from Step 5 broken into 8-bit words. The first thing to do is to add 0s to the end of the last word to make sure all words from Step 5 have 8 bits. You can see this in column AC. Then we keep adding new words (11101100 and 00010001) repeating in that order until we have 13. In column AD I identify which words need to be filled and then alternatively put Wordfill1 and Wordfill2. Column AE finishes it off by actually inserting the fill words.

Steps 7-10 involve some complex math to produce the error correction bits. The details of this are beyond the scope of this article, but there is plenty of stuff you can find on Google if you look around. I will describe in a very high-level way what I've done to implement this in the spreadsheet. We will be switching back and forth between decimal notation and Alpha (α) notation. This is done using log and anti-log tables which I have generated and have included in the sheet called "Galois Field 256". If you want to learn more about this, click here to search on Google.

Step 7: We need to convert the words into a "Message Polynomial" which is done by converting the binary words from Step 6 back into decimal. You can see this in column AG. Column AH has the exponent part which is shown here just for completeness, but is not used. In column AJ we convert the binary coefficient into what's known as Alpha notation

Step 8: Here we create a generator which is fixed for each version/type of QR code. The table in columns AM and AN are fixed values for the type of code we are creating (Version 1, AlphaNumeric mode with error correction level Q). For more info on how to create a generator for a different type of code, click here to search on Google.

Step 9: Here we perform the polynomial division using the message from Step 7 and the Generator from Step 8. Of interest here for the Excel implementation is that I had to create a method to repeatedly perform an XOR step. As I wrote at the beginning, I wanted to do all of this without writing any VBA code and there is no XOR function in Excel. You can see how I've implemented this in the XOR sheet.
NOTE: Step 9 has a known issue in it. The result of the XOR will always correctly give 0 as the first character. In some cases, the second (and subsequent) characters can also be a 0. If this is the case, then the MULT step must take the first non-zero digit. I have not implemented this and therefore the QR generator will fail under these circumstances. These are rare and hard to find occurrences, but if you want to try it out, trying to encode this string will fail: "2747585511"

Step 10: Phew, we've made it through all the tough math and it's pretty smooth sailing from here. The last column from Step 9 are the error correction words.

Step 11: We combine the first 13 words that we generated in step 6 and add the next 13 words from step 10. This is what we will be encoding into the QR code.

Step 12: Combining all the words together, we have our full binary string ready to populate into the QR matrix.

You can now switch to the QR Code sheet to see how we finish it off. I have copied the finished string from Step 12 to cell AW21 in the QR Code sheet for simplicity.

The QR code is drawn by turning pixels on or off. I have implemented this by using 1s and 0s in a matrix'ed set of cells (columns Y to AS). Using conditional formatting, I have set a 1 to be a black-filled cell and a 0 to be a white-filled cell. The trick is now to just create the matrix.

Step 13: There are some cells in the 21x21 matrix that always must be set in a particular way. These are the "position detection" cells and I have marked them off with a 1 and they are marked in green. There are also cells called "timing cells" which are always in a particular pattern. I have marked them in blue. We also need to load the data we created in Step 12 into the matrix. You can see in the matrix that stretches from cell B2 to V22, I have marked off where each bit goes. Starting at the lower left we put the first bit (that we created in Step 12), and move around the pattern as indicated. You can see how they all fit together in the matrix from B26 to V46.

Step 14: We're not quite done yet. Because the code we create could be difficult to read (e.g. if there are too many black pixels clustered together, etc.) we need to create 8 different versions of the code and select the best one based on a formula. To do this, we create a "mask" and filter the raw data (B26..V46) through it. Each mask is based on a formula which I will show you below. To tell the reader which mask is being used, we fill what I've labeled as the orange cells with a code to tell the reader which mask we're using. The table of codes can be seen in AV3..AW10. These are also repeated for the sake of ease down column AW at each code. You can see that in each block, the orange cells contain the bits we need to identify each mask.

Step 15: The matrices starting with B50..V70 (B74..V94, etc.) have been filled in with the mask formula. The mask is simply calculated as a function of the row/column of each cell.

1. If (row + column) mod 2 is 0
2. If (row) mod 2 is 0
3. If (column) mod 3 is 0
4. If (row + column) mod 3 is 0
5. If (floor (row / 2) + floor (column / 3) ) mod 2 is 0
6. If ((row * column) mod 2) + ((row * column) mod 3) is 0
7. If (((row * column) mod 2) + ((row * column) mod 3)) mod 2 is 0
8. If (((row + column) mod 2) + ((row * column) mod 3)) mod 2 is 0

If you look at the forumlas inside the mask matrices, you'll see how I've implemented them as Excel functions. Boolean functions in Excel normally return True/False, but if you multiply them by 1 you get 1/0 which is what we need.

Step 16: We now combine the raw data (Step 13) with the mask such that whenever the mask has a 1 we change the raw data to the opposite of what it is (from 1 to 0 or 0 to 1). Whenever the mask has a 0, we leave the raw data alone. The math to do this is quite easy and is done in the formulae hidden under the actual QR codes we've generated (Cells Y50..AS70, etc.). Using conditional formatting, we just set Excel to make the cells all white or all black based on the number underneath. As you can see, we create 8 QR codes (one for each mask). Officially, the next step is to pick the "best" one, but in practice, my iPhone QR reading app can read all of them... so find one that you like, test it and use it! If you really want to be compliant with the standard, you can Google how to pick the "best" pattern by clicking here. 

So the other day this pops up on my iPhone 5 and click cancel. Everything goes back to normal. Then I get this again a few more times over the next few days and if I click Accept or Cancel, I just get back to the normal screen and continue as normal. As this is a very benign screen I ignore it.

But then curiosity gets the better of me and I do a Google search. Someone, somewhere must have had this issue, but doing a search for "Format ErrorA" on Google gives absolutely nothing.

I go to the Apple store and show them a screenshot (since I can't reproduce the error on demand) and they seem puzzled as well. They've never seen this. Then they do a bit more research and conclude that they don't know what this is.

Then one of the über-Geniouses at the Apple store says that it reminds him of an error caused by a faulty SIM card and that I should contact my telco to get a new SIM card.

Then all of a sudden, after a few days, these errors stop occurring. Has anyone else seen this? Any idea what it is?

Out of the blue this morning, my phone was stuck with a blank screen and the red and green lights stuck on. No buttons did anything and rebooting the phone (via unplugging it) simply returned the phone to the solid red and green lights.

After some digging around, I found the following process to be successful:

You will need:

• A Windows computer with an Ethernet port
• A Cross over Ethernet cable or a spare Ethernet Switch or Hub
• A Broken 7912
• A Power Pack for 7912 (not a PoE switch or Ethernet power injector)
• A Copy of TFTPd32 http://tftpd32.jounin.net/tftpd32_download.html

Follow these steps exactly:
1: Disconnect the computer from your current network

2a: Connect cross over cable to the computer and switch port of the phone
~or~
2b: If you are using a switch or hub, connect both devices to the switch or hub, but they should be the only devices connected

3: Configure a static IP on the computer and disable any firewall.
3a: Use an IP of 192.168.1.1
3b: Use a Mask of 255.255.255.0
3c: Gateway and DNS can be left blank

4: Install and open TFTPd32 software

5: Click settings and modify the following:
5a: In the GLOBAL tab, uncheck everything except TFTP Server and DHCP Server
5b: In the TFTP tab, set a Base Directory to somewhere convenient
5c: In the TFTP tab, set TFTP Security to None
5d: in the DHCP tab uncheck all DHCP Options

6: Restart TFTPd32

7: Click on the settings tab and modify the following:
7a: In the DHCP tab, set IP pool starting address to 192.168.1.2, size of pool 1, mask 255.255.255.0, additional option 66 and 192.168.1.1, then click OK

8. Click on the Log viewer tab

9: Plug in the power of the phone and watch the logs.

You should see something like this:

Rcvd DHCP Discover Msg for IP 0.0.0.0, Mac 00:11:BB:0D:E7:AE [01/08 16:20:58.093]
Client requested address 0.0.0.0 [01/08 16:20:58.093]
DHCP: proposed address 192.168.1.2 [01/08 16:20:58.093]
3300 Request 2 not processed [01/08 16:20:58.113]
Rcvd DHCP Rqst Msg for IP 0.0.0.0, Mac 00:11:BB:0D:E7:AE [01/08 16:20:58.113]
Previously allocated address 192.168.1.2 acked [01/08 16:20:58.113]
3300 Request 2 not processed [01/08 16:20:58.134]
Connection received from 192.168.1.2 on port 16156 [01/08 16:20:58.144]
Read request for file (cp7912r.zup). Mode octet [01/08 16:20:58.144]
File (cp7912r.zup) : error 2 in system call CreateFile The system cannot find the file specified. [01/08 16:20:58.144]

You can see that it is looking for a file called cp7912r.zup. If you don't get to this point, go back to step 7a and use option 150 instead of 66.

Now for the difficult part. You need to get this file from somewhere. It turns out that if you manage to get a file called CP7912080001SIP060412A.sbin  you can simply rename it to cp7912r.zup and put it into the base directory (step 5b above).  If you do this successfully, the log will look like this:


Connection received from 192.168.1.2 on port 16157 [01/08 16:22:52.288]
Read request for file (cp7912r.zup). Mode octet [01/08 16:22:52.288]
Using local port 1251 [01/08 16:22:52.298]
(cp7912r.zup): sent 655 blks, 335194 bytes in 13 s. 0 blk resent [01/08 16:23:05.396]

This will indicate success and you will see the phone reboot. In my case, the phone completely went back to normal without loosing any of its prior settings.

So really the hardest part of this is to find the .sbin file mentioned above. As a hint of where to get it, do a Google search for CP7912080001SIP060412A.zip which is an archive that contains this magic file.

One of the best parts of winter is the fact that chestnuts come into season. Ever since I was young, I've enjoyed eating chestnuts whole and in various recipes (one of my favourites being gesztenyepüré). However, anyone who has cooked with them knows how hard it is to cook them just right and remove the two layers of the shell. 

This winter I was determined to find a quick way to cook up a few chestnuts to eat whole for dessert after a meal and I think I've cracked it! (no pun intended)

Begin by slicing an "X" through the shell with a sharp knife as shown in the picture on the right (click on the photo to enlarge). You need to cut about 2/3 into the chestnut (but don't cut fully through).  Then, you need to put a bit of water on a plate - a teaspoon or so - and place the chestnuts on the plate.

Cover it as shown on the picture to the left and microwave it for 33 seconds. It might be different for different models of microwave, but 33 is the exact time I need on our 1200W model. Also, 33 seconds is perfect for 3-4 chestnuts, the time is likely to vary if you add more.  Occasionally a chestnut will explode, which is another reason for covering the plate.

If all goes to plan, the chestnuts will start hissing at about the 20 second mark and will have opened slightly by the time the microwave beeps as shown in the picture to the right. (click on the photo to enlarge)

The next step is to just peel the shell off (and you'll notice that both the inner and the outer shell come off together) and in a about a minute end-to-end you now have what you see in the photo below (click to enlarge) - ready to eat!

Having lived in the U.S. for the first 10 years of my driving career, I've become accustomed to thinking of fuel economy in terms of MPG (miles per gallon). Although I have been living and driving outside of the U.S. for more than 10 years I still have MPG stuck in my head. My cars now all indicate l/100km which is probably a better measure, but I still like to sometimes do a quick conversion to MPG. Our latest car is a Diesel and sometimes I'm up for a bit of hypermiling when I've got the whole family in the car and I don't want to listen to my wife complain about speeding. There are tons of online / offline / mobile ways of converting between mpg and l/100km, but sometimes the easiest way is to just have a table to look at.

From a quick Googling exercise, nobody seems to have made available an easy conversion table, so I cooked one up.

Click Here for a PDF file that you can print out and have handy in the car.

I've zoomed in to the particular range that I'm interested in (this is what I generally get with my car). If you have a gas guzzler or some fancy new eco wonder, here is the Excel file so you can modify the chart and make your own. Just set the axes you want, the underlying data is already in the spreadsheet.

Last weekend I bought an HD set top box for my old TX-66PW1050A CRT TV. The default setting of the set top box was 1080p and so when I plugged it in, I wasn't able to see a picture. I eventually used another TV to set the box to 576p but it now seems that the TX-66PW1050A has developed a really bad pincushion distortion as a result of the ordeal.

Turns out, I'm not alone (and here is another example). Doing a bit of searching around the Web I found various posts from people who had the same problem. Apparently, the TX-66PW1050A can be permanently damaged as a result of taking a 1080p input.

I wanted to see if I could fix this problem by accessing the Service Menu on the TV. This is the menu that allows for some fine tuning of the configuration that normally Panasonic would only want done by technicians. I soon found that it's impossible to find the Service Manual for this old model online (although strangely, other similar models still have their Service Manuals available). I was also annoyed to find that the codes to access the service menu on the TX-66PW1050A were not the same as other models.

Through trial-and-error, I found out some of the codes. As there was nowhere else on the internet that had this documented, the purpose of this post is to share what I know. The bad news is that I wasn't able to fix the pincushion problem, but I did learn a lot more about this model of TV than I otherwise ever would have wanted to!

There are two modes that might be of interest: Self Check Mode and Service Mode.

Self Check Mode: To access this mode, you need to press the VOLUME DOWN button on the TV (behind the flip down panel on the front) simultaneously with the TIMER button on the remote (which is behind the flap). It will momentarily say Self Check on the top right, the screen will momentarily go black and then you will get a screen that looks something like this.

Screen from Self Check Mode

As you can see, it passes the self check despite the obvious pincushion problem. The black rectangular box to the left of Option 9 is a mystery. This appears to be a "read only" mode, as you can't seem to change anything.

Service Mode: To access this mode, push the TIMER button on the remote to set a 30 minute sleep time (i.e. press it once). Then press the VOLUME DOWN button until the volume setting is at 0. Then press the VOLUME DOWN button on the TV simultaneously with the REVEAL button the remote (this is the button to the left of the 0 with the little plus inside a pictogram of a CRT).  You will briefly see an informational display with the mode you are in and then you will see the screen as shown below.

Screen from Service Mode

In this mode you can scroll up and down using the arrow keys on the remote. You can also change the option settings using the left / right arrow keys, but after playing around with these, I couldn't see anything actually change in the image. I would be interested to hear if anyone out there knows what these options are. Incidentally, you will notice that these options are the same (except for Option 10) as what is shown in the Self Check mode above.

You will also notice CHK written on the top right of the screen. If you press the 1 or 2 button on the remote, you will then scroll (forward and backward) through different CHK options. You can then use the arrow up/down buttons to scroll through the sub settings in each CHK mode and then to actually change the settings you use the arrow left/right buttons.

Screen from CHK Mode 5

In the example above, we are setting the Gain. The default value is 56 (the pink number above the word Gain) and the current setting is 56. If I push the right button, the Gain increases. Some of the settings can go above and below 0, and other settings can only be positive numbers.

With these CHK settings, you can definitely see a change in the picture (they set things like colour, horizontal position, etc.)

Unfortunately, in the end I never did find the settings to fix the pincushion effect, so I'm afraid this TV is headed to next e-waste recycling pickup.

This afternoon the phone rang with one of those phishing scammers (documented here, here, here and here) that tries to install malware on your computer or otherwise scam you. The last time they called, I didn't have time to mess with the guy, but this time I was prepared and had some time to burn. I had created a totally quarantined Virtual Machine sandbox for them (and me) to play with, and I was ready.

I mostly did this to appease my curiosity about how the scam actually works... I took notes and screen shots as it was happening in case you are interested. The story is below.

The scam starts with a cold call from a guy with a heavy accent who introduces himself as a technician from Microsoft who has detected a problem with my machine. He then confirms that I am running Windows XP and then asks me how long it takes for my machine to boot up. I tell him about a minute and he says "Oh no, something must be wrong - it should only take about 5-6 seconds."

He then tells me he can remotely diagnose my problem and that I should go to my machine and follow his instructions. I boot up the VM and we're off.

First he has me click on START and RUN and then load the Event Viewer (eventvwr.exe).

He then asks me to click on a bunch of things (Properties, and un-check Information/Success Audit/Failure Audit) to basically remove anything except the errors and warnings. He then asks me if I see any Warnings or Errors. Of course, at this point I can only see Errors and Warnings. When I tell him that I do he says "Oh my gosh, there is something wrong with your computer, you have a virus!" For those that don't know, this is a perfectly normal screen on a virgin new installation of Windows XP. In fact if you did the same on a system that's a few months old, you'd say many many more of these warnings and errors. Again, this is perfectly normal.

He then says to open up Task Manager to check the performance hit that I have from this virus. I tell him that the CPU usage is 2% (which is normal) and again I get a "Jees, that's bad" it should be 100%, but the virus has taken away 98% of the system capacity. He then asks for the size of my PF (PF Usage) which I tell him is 148MB and he says it should be 3000MB (3GB). He also says that the yellow line in the "Page File Usage History" graph should be blue - the yellow is a sign of a critical warning. Of course, this is all bullshit - in fact the Task Manager Screen is showing a perfectly normal system.

He then tells me that he understands I would be skeptical of his call and that I should go to http://www.desksense.com which is his company and I can see that they are Microsoft Certified. Look, there is a Microsoft logo on top of the screen. It must be true! Incidentally, the guy said that I could verify that it's him by calling him back on the phone number 0280144592. I didn't bother trying, but I would have imagined that such a large company wouldn't just have a VOIP Sydney dial-in number.

He also asks me to go to the Testimonials and Awards section to see how well regarded they are. The Awards are kinda funny, I'm sure you've all heard of these prestigious awards.

Now that I trust that he is from Microsoft, he tells me to go to http://www.teamviewer.com and install their remote administration software. He tells me that TeamViewer normally costs $150 but he is giving it to me for free as part of this service. He then transfers me to his colleague who then takes over the call.

I install TeamViewer and give him my machine ID and password. What bad could come of this? :)

He then uses these details to connect remotely to my computer so that he can control it. You can see he has full control.

Also, he does a bunch of talking during which other people also seem to connect to my machine. About 5 separate people connected to my machine while he was talking. Don't quite know what they were doing - maybe looking for some interesting personal files.

The colleague, who can now remotely control my computer, loads up prefetch (START - RUN prefetch) which will find the viruses... and WOW! I am infected by the Rundll32.exe virus and the Update.exe virus!!!!! OH MY GOD! :) Again, these are all perfectly normal screens. In fact, prefetch isn't even a program, it's just a directory.

He now tells me that he will "upgrade" my CPU to 100% and upgrade my memory to 3GB (which I will see in my page file). To do this, I need to go to http://www.logmein123.com and then punch in the ID 879463 (which is a top secret number that I shouldn't tell anyone... ooops, did I just reveal his secret?) and speak to Mark Brown who is the Senior Microsoft Engineer who will handle my case. At this point it becomes an online chat and I can't hear Mark Brown's voice, we only type to eachother.

Mark now has remote control of my machine and he then installs Mozilla (which he tells me normally costs $100) for absolutely free!

Then we go to http://www.gitsolutions.net

and he takes me to the pricing page where I need to pay $145 to go on... I humour him by going to the shopping basket stage, but stop when I actually have to put my Credit Card number in there.

This is where my "internet connection" suddenly went down and we couldn't continue. We had been online for just over an hour at this point and I think they really smelled the $145 because they went through great lengths to call me back and offered to check with my ISP about what's wrong. Anyway... I then got bored and closed it all down. This didn't stop them from trying to repeatedly call me for the next hour or so.

So... just saved you an hour in case you wanted to know how this scam works :)

I imagine the next steps are left to your imagination, but by now they would have full control and access to my computer and any connected drives as well as my credit card details and address info. All in an hour's work.

Incidentally, if you get a call like this, you can quickly shut the guy down by either saying you don't have an internet connection, you have a Mac or you left your laptop at work and don't have another computer. They will just quickly move onto their next target.