I am in an Excel popularity contest (talk about an oxymoron). A couple Excel bloggers were invited to compete in “Excel Dashboard contest” and naturally this is what I spent last Saturday night doing. The winner is declared by how many YouTube likes each effort earns, which essentially means I have no chance of winning because a couple competitors have pretty large followings.
However, I would like to at least have a good showing, so if you have a moment, I would be most grateful if you gave my video a “like”. Requires a youtube or gmail account and just clicking the “like” (thumbs UP) button located above where the number of video plays is displayed.
Don’t feel like you need to watch the whole video unless you’re suffering from insomnia and are seeking a cure.
Use Mailvelope to send and receive encrypted email. Uses PGP encryption, the same method Edward Snowden used to reach out to reporters in 2013. When used properly, with the private key protected, there is no known successful direct attack against PGP. Most attacks focus on indirect methods such as stealing a password, keystroke logger or snooping over someone’s shoulder.
1) Install Mailvelope on Chrome or Firefox
2) Create a public/Private key for yourself
3) Share your PUBLIC key (NOT YOUR PRIVATE KEY)
4) Collect your friends Public key and import them to your Mailvelope Key ring.
5) Message away with complete privacy.
Always remember to lock your computer, and don’t share your private key or give anyone access.
I have been experimenting with usage of White Board animation. Here is an effort to address the terrible problem with our First Past the Post Voting System. There are much better videos out there explaining this, but here’s my effort to explain its necessity.
I found an amazing website that does a brilliant job of using a a couple simple concepts to combine for an incredibly useful tool. OriginStamp.org is a true gift to the world by developers André Gernandt and Bela Gipp. In conversation with them they told me, “We started this project just for fun and didn’t expect so many people to use it.”
The site’s popularity doesn’t surprise me — it’s awesome! They have created a FREE service that allows anyone to prove they possessed any type of electronic file before a specific date. The electronic file could be as simple as a string of text, or as massive as a movie file.
I’ll dive into the technical details later, but consider a couple basic applications:
Someone has written an amazing script and wants to have it logged as their work, before sharing with a publisher.
Someone moves into a new rental property, and takes extensive video of the condition of the property, which they want to archive on the day of their move-in. By archiving the footage, the landlord cannot argue that the video was taken on the move out date.
Someone wants to log a text prediction. Example would be if I said, in 2015, “I, PG CodeRider, predict the Cubs will win the 2016 World Series in a game 7 thriller against the Indians!” it would be pretty impressive!
Without OriginStamp, creating verifiable proof of a file’s existence, before a certain date is difficult (having a “saved as” date with the file doesn’t count). Users would likely have to defer to a third party to provide proof of ownership. This lends itself to the following problems:
The third party site likely costs money.
The third party site may disappear.
The third party site may not remain credible to the rest of the world.
The third party site might experience a server crash, hack, or accidentally delete your file.
OriginStamp avoids all of these problems. The simplicity of their approach, combined with their robust method of validation, makes it truly an elegant creation. The site ingeniously leverages the bitcoin blockchain as a point of reference. Because the blockchain is a decentralized entity with literally thousands of people monitoring its integrity, it is impossible to manipulate historical entries. Additionally, due to this same decentralized nature of the blockchain, it is difficult to imagine a scenario where the Blockchain would suddenly cease to exist. Thus a user can rest assured knowing their claim is verifiable so long as the internet remains functional and Bitcoin continues to have even a small percentage of people using it.
How You Can Use This Tool (without actually understanding it)
For those of you that just want to “trust me” and believe this works, without understanding why, here’s a step by step guide of how you can use it:
1) Create a FINAL VERSION of a file or text that you want to verify. It is critical that not a single change occurs in your file, or it won’t be verifiable in the future. Example is if you write a million word novel, once you submit it, you can only reference that version you submitted. A change to a single character will make the entire file incapable of being proven to be your file at the time of the transaction.
I’m going to go through this process by validating a screenshot I created with my 2016 presidential predictions. Again, the “Final Version” is very key, and it’s easy to overlook how it’s possible to accidentally alter. In my case, I cannot simply post a copy of the JPG file of my prediction and maintain the integrity, because when a user downloads the file, certain attributes such as the “saved date” would be different than the original. Thus I have posted a zipped version of my jpg file, which if someone were to download and extract the jpg file, would ultimately be my FINAL VERSION.
It’s also important to note that you include something that references you as the creator in the file, so that no one else can claim it as their work. My file has my name in the screenshot.
In my example, the exact SHA-256 hash of my FINAL VERSION is: 3742fd0fcebd60f38995429e736a1e2f3f040ea367c21ce87cb1b9bcd89e5d89
If you aren’t certain you’ve hashed correctly from a 3rd party site, you could cross check with a single letter of text “a” which should result in: ca978112ca1bbdcafac231b39a23dc4da786eff8147c4e72b9807785afee48bb
Make sure you have a copy of your hash, as well as the original copy of your file.
3) After submitting your file, you will get a notification saying your hash has successfully been created. Note that OriginStamp will not “submit” it to the blockchain until about 7PM East Coast time. They only submit free submissions once a day, to keep their costs down, and depending on how busy the blockchain is it may take up to 2 days to register. If you’re in a hurry to get your hash submitted, they offer a premium service to accomplish this where they wallop you with a colossal fee of $1 to get it in right away.
4) If you trust OriginStamp to remain in existence forever, you need not do anything further. When you need verify the date of your file, go to their website, click Verify Stamp, and enter your hash, or drag in your file, and the site will tell you when it was submitted. However if you wish to be able to validate your file without the existence of Originstamp, you’ll need to collect a few more pieces of information. After waiting a day or two, you’ll need to revisit OriginStamp, click Verify Stamp, enter your hash or upload file and it will take you to a confirmation page. On this page, it will list of all the hash submissions as well as the Check Sum hash. You should store both of this with your final version file. The list of all hashes can be a somewhat long now as the site gains popularity.
Hopefully you would never have to defend the legitimacy of your file, but if you did, you should have a pretty convincing case. Of course, I have no idea how a jury would react to this information, or if it would even be admissible as evidence. However, to illustrate what you have, below is how I would argue the authenticity of my Presidential prediction if someone was accusing me of being a fraud in a court of law
Me: “Your honor, I did in fact create this JPG on 10/31/2016. To prove it I used a site called OriginStamp. This site took my file and hashed it using the SHA-256 method. If you go to OriginStamp, they will confirm this transaction.”
Accuser: “What the heck is OriginStamp?!? Judge, objection! I’ve never heard of this site. How can we trust its validity?”
Me: “Okay forget going to OriginStamp, we can walk through what they did. OriginStamp created an SHA-256 hashed text of my file (see above). OriginStamp then took ANOTHER SHA-256 hash (the Check Sum) of all the other records they received that day. I have a list of these and the SHA-256 hash of these can be conducted on numerous sites.
“As is the nature of SHA-256 hashes, they are relatively easy to calculate in one direction, but impossible to conduct in a reverse manner. Stated differently, I can claim with utmost certainty that no one on the planet can produce any SHA-256 pre-calculation string which results in the same final hash output as any of mine, without using my file or text. Such an effort would take far more than millions of years with today’s computing power.”
Accuser: “So what he has a hash that is unique? What does this prove?”
Me: “It doesn’t prove anything yet, but as a final step, Originstamp used the final Check Sum hash along with Base 58 encoding, to find a Bitcoin address to log a small Bitcoin transaction which cleared on 11/3/2016. This process can also be demonstrated on multiple websites, I have a screenshot from Brainwalletx.Github.io. A user simply needs to enter the Check Sum as the Secret Exponent to generate the address that was used. What this means is that this address was specifically used for this purpose we have outlined. The possibility that this address which comprises a hashed connection to my exact file is incredibly unlikely.”
Accuser: “So you’re saying there’s a chance…”
Me: “Technically yes, but the fact that an active address which I found with my specific JPG hash in it is beyond infinitesimal. There are exactly 2^256 possible bitcoin addresses — that’s far more atoms than there are on earth, sun, and other planets, combined. The likeliness of this series of events happening is less than me playing the Mega Millions Lottery and winning, four times in a row.”
Accuser: “Okay so how do we know you didn’t make the entry after the election?”
Me: “The blockchain’s integrity is maintained by thousands, perhaps millions of computers validating transactions. At the end of Sep 2016, the hash calculation rate per second was 2.6×10^16. This represents far more processing power than any single entity such as the United States government could direct at the blockchain in hopes of manipulating the network. It would be easy for me to find at least a dozen articles on the web, or a computer science professor to testify how unrealistic back-dating entries in the block chain is.”
Accuser: “I still think this is bogus”
Ultimately your argument would likely hinge on testimony of some math expert, but that’s my best effort to simulate how to defend the legitimacy of the transaction. Note the links in the discussion above for reference on how Base58 encoding is conducted.
Quick tip on how to slash the size of large Excel files, often by more than HALF! It’s a somewhat unknown file format .XLSB which stands for Binary. When Microsoft released Excel 2007, they switched the file format to be a zipped XML format. This enables a lot more cross compatibility with other Microsoft products like Outlook and Windows Explorer Preview. XLSB is essentially the Excel 2003 version, but with the full range of rows.
The savings on a lot of my big files has been pretty significant. Generally a reduction by about 60%. This only seems to happen on larger files. So if you have a file of 1 meg, it likely won’t get reduced by that much.
One other advantage of XLSB is that it retains VBA without a prompt, something I definitely prefer. Only drawback is if you have file linking to other Microsoft products like power-point, or even other spreadsheet programs like OpenOffice, the XLSX will definitely work better. However, most massive files exceeding 10 megs probably are probably only used in Excel.
Updated with a video. Spoiler Alert: I reduce a file size by about 60%!
Yesterday I needed to perform a two-dimensional sumif’s, and finding syntax on how to do this wasn’t easy (meaning it took more than 3 minutes). So I figured I’d flood the internet with one more example.
My Video below gives a short walk-through of it, but essentially you use SUMPRODUCT function. The syntax is basically SUMPRODUCT((Range1CONDITION=<VALUE>)*(Range2CONDITION=<VALUE>)*DATARANGE).
In my initial example, I use formula:
Order doesn’t matter in the above statements. What the function is actually doing is looping through each cell in the DATARANGE, testing if all the criteria is true. If so, it assigns a 1 in the first two fields, and the test, for each cell in the range, and if true, it assigns a 1 to it.