Last time I showed you how to set up a reasonably complex design in a spreadsheet: a common emitter bipolar transistor amplifier. Having the design in a spreadsheet makes it easy to do “what if” scenarios and see the effects on the design almost immediately.
Another advantage that spreadsheets offer is a way to “solve” or optimize equations. That can be very useful once you have your model. For Excel, you need to install the Solver add-in (go to the Excel Options dialog, select Manage Add-Ins, and select the Solver Add-In). You might also enjoy OpenSolver. You can even get that for Google Sheets (although it currently lacks a non-linear solver which makes it less useful for what we need).
Continue reading “Optimizing the Spread: More Spreadsheet Circuit Design Tricks”
Accountants and MBAs use spreadsheets to play “what if” scenarios with business and financial data. Can you do the same thing with electronic circuits? The answer–perhaps not surprisingly–is yes.
Consider this simple common emitter amplifier (I modeled it in PartSim, if you’d like to open it):
In this particular case, there are several key design parameters. The beta of the transistor (current gain) is 220. The amplifier has an overall voltage gain of about 3 (30/10). I say about, because unless the transistor is ideal, it won’t be quite that. The supply voltage (
Vcc) is 12 volts and I wanted the collector voltage (
VC) to idle at 6V to allow the maximum possible positive and negative swing. I wanted the collector current (
IC) to be 200mA.
Continue reading “Circuit Design? Spread the Joy”
When you think about serial communications, Microsoft Excel isn’t typically the first program that springs to mind. But this spreadsheet has a rather powerful scripting language hidden away inside it, which can, with a little coding, be used to send and receive data over your serial port. The scripting language is called Visual Basic for Applications (VBA), and it has been a part of Microsoft’s Office suite since 1993. Since then, it has evolved into a powerful (if sometimes frustrating) language that offers a subset of the features from Visual Basic.
It can be a useful tool. Imagine, for instance, that you are logging data from an instrument that has a serial port (or even an emulated one over USB). With a bit of VBA, you could create a spreadsheet that talks to the instrument directly, grabbing the data and processing it as required straight into the spreadsheet. It’s a handy trick that I have used myself several times, and [Maurizio] does a nice job of explaining how the code works, and how to integrate this code into Excel.
If you’re looking for other ways to leverage this Excel feature, consider watching movies at work or building a virtual machine inside of your sheets.
[Ronnie] recently posted a new chapter in his adventures in malware deconstruction. This time the culprit was an infected Excel spreadsheet file. The .xls file was attached to a phishing email claiming to be related to a tax rebate. With tax season in full swing, this type of phishing message would be likely to be opened by an inexperienced user.
[Ronnie] saved the file to a virtual machine to prevent his real workstation from getting infected. He then opened it up in Excel and noticed that it immediately attempted to run macros. A macro is essentially visual basic scripting that runs inside of the spreadsheet file. You can use it for simple automation, cell formatting, or do even more complicated tasks like reach out to external websites and pull information. This malware focused on the latter.
[Ronnie] used the alt + F11 shortcut to view the macros. Unfortunately the attackers had password protected them. [Ronnie] wouldn’t be able to view the macro code without knowing the password. Luckily, he learned of a surprisingly simple trick to completely bypass the macro password. He opened up the .xls file in Notepad++ and located three keys; CMG, DPB, and G. [Ronnie] then created and saved a new blank .xls document and password protected the macros with his own password. He opened up this new file in Notepad++ as well, and located those same three keys. He copied the keys from the new file into the old one, and saved the old file. This effectively changed the password of the malware file to the new one he had set for his new file. This is a nifty trick that apparently only works on the older .xls formats, not the newer .xlsx format.
After loading the macros, [Ronnie] quickly noticed that most of the code was obfuscated to make it difficult to analyze. There were, however, three named modules that reference possible sandbox evasion techniques. The malware first invokes these functions to detect the presence of a virtual machine or other type of sandbox. If it detects nothing, then the rest of the malware program is decoded and executed. [Ronnie] removed these checks and then executed the macro to verify that his change had worked.
The next step was to try to view the decoded instructions. The decoded gibberish was saved to a variable. The simplest way for [Ronnie] to view the contents of the variable was to have the program create a pop-up box that displayed the contents of that variable. After making this change and running the program again, he was able to see exactly what the malware was doing. The code actually invoked Powershell, downloaded a file from the Internet, and then extracted and executed that file. In the full write-up, [Ronnie] goes even further by downloading and analyzing the executable.
[Ádám] participates in a competition called KöMaL. It’s a 9-times-a-month journal for junior high and high school students featuring math and physics problems. [Paul Erdős], one of the most published mathematicians of all time, was a huge participant and by far the most notable student to crack open a copy of KöMaL in its hundred-year history. [Ádám] was trying his hand at a problem in Excel, but the official rules prohibit the use of Excel macros. In a daze, he came up with one of the most clever uses of Excel: building an assembly interpreter with the most popular spreadsheet program.
This is a virtual Harvard architecture machine without writable RAM; the stack is only lots and lots of IFs. The instructions – mostly load, MOV, JNZ, INC, and CMP solves this problem, examining two inputs to see if they multiples of each other. If you’re wondering, an example cell from [Ádám]’s Excel sheet looks like this:
[Ádám] has provided his Excel solution to the problem, available on the hackaday.io. It’s in Hungarian which really shouldn’t matter since it’s basically Excel and a pseudo-x86 instruction set. but the column labels will require a bit of Google Translate.
We got a case of the Mondays just reading about [Sascha]’s work environment. Get this: every morning, first thing, the whole team gets together to check in and share how they’re all feeling. And they can’t even be candid about it—there’s actually an approved list of feeling descriptors, both good and bad. It’s an admittedly big list that includes, interestingly enough, both ‘tortured’ and ’embarrassed’. Yeah. We think something like group t’ai chi on the roof each morning sounds a lot more relaxing. Since [Sascha] is between a rock and a hard place on this one, it was time to let chance take over. He raised his HaD-imprinted Trinket skyward and Can I Borrow a Feeling? was born.
The gist is simple: [Sascha] abstracts his disposition out to either ‘good’ or ‘bad’ and pushes the corresponding button. The Trinket accesses an array and returns a randomly selected feeling to the LCD. Since the official list of feelings is about 300 words long, [Sascha] has to push the data into PROGMEM. He used good old Excel to split the list in twain, and her formulas came in very handy for centering the result on the LCD. Once [Sascha] knew how it would all fit together, he designed a cool enclosure in CorelDRAW and turned on the laser cutter. See the Spreadsheet of Acceptable Words for yourself on GitHub, and pick up the code and enclosure file while you’re there.
There’s still time to enter the Trinket Everyday Carry Contest. The main contest runs until January 2, but we’re having random drawings every week! Don’t forget to write a project log before the next drawing at 9pm EST on Tuesday, December 30th. You and all of the other entrants have a chance to win a Teensy 3.1 from The Hackaday Store!
The Excel subreddit exploded earlier this week when redditor [AyrA_ch] shared his custom spreadsheet that allowed him to play video files on a locked-down work computer. How locked down? With no access to Windows Media Player and IE7 as the only browser (all plugins disabled, no HTML5), Excel became the unlikely hero to cure a 3-hour boredom stint.
Behind the cascade of rectangles and in the land of the Excel macro, [AyrA_ch] took advantage of the program’s VBA (Visual Basic for Applications) functions to circumvent the computer’s restrictions. Although VBA typically serves the more-complex-than-usual macro, it can also invoke some Windows API commands, one of which calls Windows Media Player. The Excel file includes a working playlist and some rudimentary controls: play, pause, stop, etc. as well as an inspired pie chart countdown timer.
As clever as this hack is, the best feature is much more subtle: tricking in-house big brother. [AyrA_ch]’s computer ran an application to monitor process usage, but any videos played through the spreadsheet were attributed to Excel, ensuring the process usage stayed on target. You can download it for yourself over on GitHub.