Importing Quicken Data into gnuCash

Before I went the the procedure of cleaning up my quicken data I attempted an import which failed, of course. Worse, there was no indication of what caused the failure. But I knew my data file was a monster, so I set out to clean it as already discussed.

After cleaning my Quicken data file so that I only had about 18 months of transactions, only open securities, and no unused accounts I tried the import again.

I still have trouble, though I don’t recall the exact error. What I found is I needed to create a very basic chart of accounts before trying import the data. Once I did that, the data imported without failure.

I won’t try to kid anyone. The data imported but it was quite a mess. I easily spent 8 hours cleaning. And that was for maybe 300 transactions, 400 max.

First I had to organize all of the accounts into some sense of structure. Bank accounts in Assets, Quicken expense categories in expenses, etc.

With that done I started checking every asset and liability to make sure the accounts matched between gnuCash and Quicken. I don’t think a single account actually imported cleanly. The largest issue, for me, was a lot of duplicate transactions. I quickly realized most of these were UNCLEARED whereas everything else was cleared. So I would run down the register looking for UNCLEARED transactions, look for a dupe and delete the uncleared.

After that fix, I would still have to go thru the entire register for each account for both systems to verify everything was correct.

Then I checked all transactions for all securities. It did very well on those. Only a few issues.

I believe both sets of books now match. Next up, I need to figure out how to download transactions from the bank.

Cleaning out Your Quicken Data File: Deleting all Historical Transactions, Securities, and Accounts

[Note: please DO NOT ask me questions about this procedure. I did it twice myself and never expect to do it again. I will not remember any details and will not be able to provide any more help that what is described within this procedure. ]

This procedure was done using Quicken 2020. The exact sequence of buttons may change in the future.

My Quicken Data file is filled with decades of old data, long forgotten closed accounts, and stock transactions from now non-existent companies (like MP3.COM!).

If I’m going to migrate my current data to gnuCash, I’m sure I need to get rid of as much history as possible to make the migration less of a headache.

I spent several very long sessions figuring out how to remove all of this history. The old securities were particularly a problem. The process is complicated enough I decided to document it here in case any one else decides they need to do the same.

You need to backup your live data and work on a copy. Chances are you will need to go thru the process several times until you figure out exactly what works for you. As I was going thru the process, I would check point my progress by exiting quicken and copying the data file. Then if I screwed something up, I only had to return to the prior checkpoint or worst case a few checkpoints back.

Deleting Old Accounts

• As already mentioned, copy your live quicken file and work on a copy of the live data. Once you are done you will have a file to import into gnuCash and still have your live data file so you can continue to use quicken, even if just for historical data.
• Do a screen capture of quicken’s left panel that shows the balances of all of your accounts and your net worth. Once you are done, you will want to verify these all still match.
• With your copy of the quicken file open, Select File | File Operations | Validate and Repair. In the dialog box, select validate file and click on OK. Depending on your data file size, this can take a while. About 5 minutes for me.
• Open the Account List (ctl-A). Select ‘Show Hidden Accounts’ box. All of my old unused (zero balance) accounts get listed this way. I don’t think I have ever deleted an account so I have a bunch of them.
• The next step is to reconcile all of these hidden accounts with a Zero balance. The year-end process will not delete old transactions unless they are reconciled.
• I start at the top of the account list, open each old account, reconcile it (ctl-R) using a zero ending balance.
  • Closed Brokerage accounts  should be reconciled in the same manner.
  • For Asset(Property) and Liability accounts, you cannot do a ctl-R reconcile. Instead:
    • Select the transactions you want to ‘delete’ in year-end
    • Edit | Transaction | Reconcile State | Reconciled to mark them reconciled.
  • You may have other non-hidden accounts that you want to reconcile. For example I have a CASH, AR, and AP account that are catchalls and have decades of transactions that really aren’t important to me.
  • I’ve had a couple of cases where reconciling an Asset account has messed up another account total. I had to run transaction registers of before and after to find the screwed up transactions and fix them. I suspect the problem was due to some corruption in the data file.
• You are ready to run Year End (YE) which will delete all transactions OLDER than a date you specify.
  • YE will not delete unreconciled transactions.
  • YE will not delete any transactions dealing with a security.
  • To start the YE, File | File Operations | Year-End Copy
    • Select I only want transactions in my current data file …
    • Typically this would be Jan 1 of the year for which you want to keep in your new quicken data file.
    • Click OK. This takes a fair amount of time.
  • Review the YE:
    • One irritating issue I’ve found – if windows file explorer has your quicken data file selected, quicken will fail the YE w/o any indication of an error. If nothing appears to have been deleted, check for that.
    • Your account balances should match what you had at the start.
    • Examine each account. In general, they will start on the specified date. If you have transactions dealing with securities or transactions to an asset/liability that you didn’t reconcile then those will still show. Generally that is fine.
• At this point I do another File | File Operations | Validate and Repair and check the validate box just to be sure something horrible hasn’t happened to the data. Check those account balances!
• Finally, you can delete old accounts!
  • ctrl-a to bring up accounts list. Check Show hidden accounts.
  • To delete an account from the list, click on the edit button, click on delete account button, Verify it is the right account, type in Yes, click OK.
  • Repeat until they are all gone!
  • Delete Old brokerage accounts if there are no open securities in them.
  • At this point, I no longer have any hidden accounts and my account balances are all still correct.

Deleting Old Securities

Deleting old securities is even messier than deleting accounts and transactions. Unless you’ve got many, you are probably best off not doing this. But here is how.

• You should still have a screen capture of your account balances.
• Take a screen capture of your open securities
• Create a new ‘TEMP’ brokerage account to hold open securities
  • Tools | Add Account | Brokerage | Use Advanced Setup | I want to enter xactions manually
  • Click on Next, use ‘TEMP’ as account name, click on next, click on next again, click on next again, Yes for no securities entered, and finally click on Done.
  • Select No for single mutual fund account and click next then click finish.
• Move Open positions (securities) to the TEMP brokerage account:
  • Select the brokerage account containing the securities to move
  • Click the settings gear icon at the upper far right
  • Click on Move transactions
  • In the Move Investment transactions window click on the Select Open Positions button.
  • In Move To Account box, select the new TEMP account.
  • Click on the Move Button, then click on the Done button.
• Archive Closed positions to Archive account:
  • Select the brokerage account containing the securities to move
  • Clicke the settings gear icon at the upper far right
  • Click on Archive Transactions
  • Click on Yes, click on Backup
  • All old transactions are in a new account named <brokerageAcctName>-Archive
• Delete this newly created Archive account. Verify net worth is still correct.
• Now you can deleting old transactions in the brokerage account linked checking account that matched those old securities.
  • Open the brokerage account linked checking account
  • Sort on Category
  • Locate and then select all transactions with a category of [Unspecified Account]
  • ctl-D to delete the transactions
  • Your Net Worth will now be wrong and the brokerage checking account will be the culprit. That’s OK for the now.
• Move open positions from TEMP account back to brokerage account:
  • Open the TEMP brokerage account
  • Click the settings gear icon at the upper far right
  • Click on Move transactions
  • In the Move Investment transactions window click on the Select All button.
  • In Move To Account box, select the brokerage account
  • Click on the Move Button, then click on the Done button.
  • Delete the TEMP account
• Correcting the Brokerage Linked Checking Account Balance
  • Open the Checking Account
  • Verify the transactions are sorted by date
  • Go to the top of the transaction list (oldest) where you will find a Beginning Balance transaction.
  • The beginning balance will probably contain some crazy big negative number. Change it to zero.
  • Examine the current balance for this account.
  • Enter a new beginning balance (in the DEPOSIT field) that is <correctBalance> – <currentBalance>
• Now, the checking account balance should be correct as well as the Net Worth
• Open Positions should match up with what they were before.
• Delete Old Securities (Finally!!):
  • ctl-Y will bring up the securities list
  • Each security that is closed and contains no transactions will have a DELETE button available at the far right of the security’s line.
  • Scroll down the list, deleting each of the securities that can be deleted.
• Final Check
  • All account balances and net worth match the original values.
  • Run Validate one more time

Extra Credit

I had one special case security that took some extra thought.

Like most (I assume) brokerage accounts, mine has a money market (MM) account that money sweeps into and out of automatically (at least in the early days) and there is the obligatory $0.02 cents of interest each month. After a couple of decades this amounted to a lot of transactions.

The brokerage account had stopped using that account several years ago (the balance was moved to another MM account) and it was essentially a closed security EXCEPT they had decided to start using it again just last month. A balance was moved back to that account and it had one month of interest.

I really wanted to get rid of all of those historical transactions and just keep the 2 recent ones.

To do this, before starting the Deleting Securities procedure, I created a new transaction against that MM security that SOLD all of the shares for $0, effectively closing that position.

After moving all positions from the brokerage account (and before deleleting the archive account), I moved JUST the 2 recent transactions from the archive account back into the brokerage account.

That completes my process for getting rid of all historical transactions, securities, and accounts in the quicken data file. I now have a clean quicken data file I can use for experiments on importing data into gnuCash.

I’m So Done with Quicken, Maybe gnuCash?

I never really had a high opinion of Quicken from the early days (I’ve been using it since the early to mid 90’s). It seems every mandatory upgrade generally brought enough problems that I would have preferred to stay with the old. I have wanted to get rid of it for years, but there really hasn’t been a great choice. Being able to download transactions from multiple accounts and pay bills via bill pay directly from the register are really time-saving. Enough to put up with the crappy software.

My old version of Quicken is 2017, so this month I was notified of the mandatory upgrade to 2020 (if I still want to do transfers with my bank).

During the upgrade I was warned that Quicken Bill Pay (QBP) will go away on May 31, 2020. Quicken wants you to upgrade to the new Bill Manager. After some deliberation I decided the best course was to just upgrade and continue to use QBP for at least a month. I do not like to make a bunch of changes at once, especially where finances are involved. Being able to handle my finances is CRITICAL (hello Quicken, do you understand this is true for everyone??).

I did the upgrade and watched carefully. The interface isn’t any worse, that I can tell, and maybe they have finally fixed some of the focus-loss issues that have driven me crazy for years (I DON’T want to continually use the mouse and that has been impossible because the current window looses focus).

All was well for a week until I was ready to pay most of my monthly bills. They went into Quicken fine, but when I attempted to do the update to transmit them to QBP, it failed. OK, fabulous, I need to pay bills, and I can’t. My creditors don’t give a sh*t that Quicken doesn’t work. It’s Sunday so I figure I can get hold of support the next day, get the bills paid and have them delivered on time since today is Sunday.

Monday I started trying to work with technical support. After a lot of wasted time with Quicken (my bank tried to help as well to no real avail), apparently the answer is Quicken 2017 used the ‘old’ bill pay and I needed to convert to the new Bill pay. I tried that repeatedly with no success and the end of Monday was coming up quick.

To pay the bills, I ended up going into the QBP web site and paying them there, and then manually recording them in quicken as well (which is exactly why I don’t want to use a bank’s bill pay). That has at least resolved the immediate problem of getting bills paid.

I have done a precursory review of Bill Manager and I am unimpressed. I have to give BM login credentials to the websites of the various companies I pay bills thru. Um, I frankly don’t know that info. Part of the reason for using quicken is to have ONE place to handle all financial transactions. I tried to setup a ‘manual’ account, but that seems to be meant for writing one-off check and supplying all info (like account no) rather than pulling it from quicken.

If I don’t use Bill Manager and use a my bank’s bill pay instead, the most critical reason for continuing to use Quicken just went away. I know gnuCash is capable of (most?) everything else I would do with Quicken.

At this point, I need to review Bill Manager in more detail to see if there is some way to just write checks to everyone like I did with QBP rather than have to provide login credentials to the website of every company I do business with.

I’m also going to start experimenting with gnuCash to see if I really can use it to replace quicken.

The adventure will be continued…

 

 

 

 

HP3000/XL Series 918 Startup and Shutdown Procedures

Quick video to remind me the proper way to start / stop my HP3000 – a procedure I do very infrequently.

Can Canned Fuel (Sterno) Boil Water?

Short answer: Yes, but slowly.

I have a small electric inductive burner I can run from my generator and a similar propane burner with a converter that allows it to run from either small green propane tanks or a larger 5G style tank.

I’m not consistent at keeping fuel supplies available for either of these cooking methods, though. So I considered getting a canned fuel stove and a supply of canned fuel I can keep stored.

I have a bucket of emergency food, but much of that food relies on having access to boiling water. So my question is: can canned fuel boil water?

Reviews and Q&A on Amazon seem to be mixed and nothing on the Sterno site (that I found) indicates it does or does not. So it is time to setup an experiment.

Full disclosure: this is not a highly accurate experiment, but it should give me a yea/nay on whether I want to rely on canned fuel.

My setup is straight forward: a stove, canned fuel, and a fairly accurate thermometer.

I used Sterno canned heat for fuel:

https://www.amazon.com/gp/product/B07J1JB562/ref=ppx_yo_dt_b_asin_title_o00_s01?ie=UTF8&psc=1

The cook stove is from Coghlan’s:

https://www.amazon.com/gp/product/B0007L8108/ref=ppx_yo_dt_b_asin_title_o00_s00?ie=UTF8&psc=1

A cuisinart sauce pan:

https://www.amazon.com/gp/product/B00008CM69/ref=ppx_yo_dt_b_search_asin_title?ie=UTF8&psc=1

and an exectech multimeter with temperature sensor:

https://www.amazon.com/gp/product/B000EX0AE4/ref=ppx_yo_dt_b_search_asin_title?ie=UTF8&psc=1

First question: how hot is the flame? In general I was seeing 330F. It would sometimes go as high as 650F, but just briefly.

I tried 3 different experiments:

• Heat 1 cup Uncovered
• Heat 1 cup Covered
• Heat 2 cups Covered

Here is a table of each experiment showing the temperature of the water as time passed:

In summary, you must cover the pan or the water will never boil. Even then it is pretty slow to get boiling.

To bring 2 cups of water to boil consumes 1/2 the 2.6 oz can of fuel. To add food to the water, get it back to boiling, and cook would certainly go thru the whole can and take roughly the full 45 minutes the can would last.

This is a little to slow for my taste, so I’ll stick to propane though I might buy a box of fuel just to use for heating (rather than boiling) food.

Feb 2020 Update:

I purchased a propane stove like this ($37 at local sporting goods store):

https://www.amazon.com/Coleman-Bottle-Propane-Stove-Green/dp/B0009PUR5E/ref=sr_1_1?crid=2J0R5LUO2TOIE&dchild=1&keywords=coleman+bottle+top+stove&qid=1582402287&sprefix=coleman+bottle+top%2Caps%2C219&sr=8-1

Then did the 2 cup water test using this setup:

I set the burner 2 notches back from high (high just seemed to me like too much heat was being wasted). It took 3 minutes, 40 seconds to heat 2 cups in this scenario.

I haven’t been keen on using 1lb propane bottles as possible emergency fuel as I don’t want to keep a decent quantity of tanks stored anywhere. But I do have 3 5G propane tanks I use in the BBQ, deck heater, and spare.

I found I can just keep a few 1lb propane bottle and refill them with this:

https://www.amazon.com/gp/product/B00VVNGW5C/ref=ppx_yo_dt_b_asin_title_o02_s00?ie=UTF8&psc=1

Instructions can be found easily on the web. It’s not the safest thing to do I imagine, but would do in a pinch.

Windows Update Blocker, WUG

One of my biggest issues with Windows 10 is the fact that it updates itself whenever the heck it wants. Yes, there is some control, but not enough. I have multiple Win10 systems that are left off for months and when I turn them on, they immediately start updating (downloading) when all I want to do is run a quick test, and just turn it back off.

With all prior versions of Windows, I turn off auto updates, then do a periodic update on all systems simultaneously. I may blow a whole morning doing so, but it is on my own schedule.

I recently found Windows Update Blocker or WUG. It turns auto updating off allowing me to do the updating on the same cycle I update all of my other machines.

I’ve installed it into a test system and I’m watching it. That system had been off for months and it’s not updating, so I it looks like WUB works as advertised.

WUB 1.5 can be found here:

Windows Update Blocker v1.5

 

Using FPCUPDELUXE to Create (Free Pascal) Lazarus Compilers of Different Versions

I use stable versions of the Lazarus/Free Pascal compiler for my production projects. Decades of experience has left me very leery of the latest and greatest of any software.

For Lazarus, that means going to SourceForge (https://sourceforge.net/projects/lazarus/files/) and using a x.x.2 or greater version of the compiler. x.x.0 is forbidden in my book and even x.x.1 typically I avoid.

This is all fine and dandy until I find a bug in the compiler, and I cannot come up with a reasonable work around.

When an error is found with no resolution, I report it to the compiler writers. Without fail, the first question will be – can you reproduce it with the trunk compiler (the absolute most recent). Not that I can blame them. The bug may well already be fixed and they don’t want to waste time chasing down problems already fixed.

This means I need to be able to create a trunk version of the compiler on demand.

In the past, I’ve manually built the Lazarus compiler on Windows system. It is  not a simple process but I know the procedure and can get a compiler running in a few hours. One downside to manually building the compiler is it will interfere with any existing Lazarus compiler, so the manual build should be done in a clean VM.

This week I found myself in the same predicament, except the bug is occurring only on the MacOS platform. I can just barely manage getting my production software to run on this platform. Manually building a compiler would be very time-consuming for me.

As I was researching the build process for MacOS I stumbled across the FPCUPDELUXEprogram. You tell it the version of Free Pascal and the version of Lazarus you want, it will download the code from the SVN along with the proper bootstrap compiler, then build everything for you. Further, the install is completely isolated from the rest of the system so any production Lazarus compiler is not impacted. YES!

I installed FPCUPDELUXE on my Mac system and used it to build FPC/Lazarus 3.0.4/Trunk. In about 1/2 an hour I had a usable compiler.

The FPCUPDELUXE program can be found here:

https://github.com/LongDirtyAnimAlf/fpcupdeluxe/releases

I needed to build the 64bit MacOS compiler, so I downloaded

fpcupdeluxe-x86_64-darwin-cocoa.zip

Note: On MacOS, FPUPDELUXE must be installed properly into the Applications folder. I didn’t do that on my first attempt and had some access issues.

When you start FPUPDELUXE you will see:

Operation is quite straight forward.

• Click on setInstallDir and select where you want the blob of code (FPC and Lazarus).
• Select the Version of the FPC compiler. Note the branches stable, trunk, and fixesn.n. These are useful if you want the latest or just the fixes.
• Select the Version of Lazarus to install. Note that Lazarus relies upon various features of the FPC compiler. It would be a bad idea to select an FPC compiler older than what that version of Lazarus was released with.
• Finally, click on the Install/Update FPC+Laz button.

About 30 minutes later you will have a running compiler. So much easier than the manual method!

Once the compile is done, you will find a link to the Lazarus program in your home directory:

You will find that a Desktop Link was built as well:

There are many other options available including cross compiling to Raspberry Pi. If / When I use some of these options, I’ll document them as well.

Compiling Specific Revision

I discovered rather than just compiling trunk, I needed trunk up to a specific revision.

Click on setup, for Lazarus Branch, select trunk. For revision, enter the revision you want (62400 in my case).

After you enter the revision, it is recalled when you restart fpcupdeluxe.

Watch Dog Timer (WDT) for ESP8266

While messing with an ESP8266 for the past couple of days, I decided I should look into how the watch dog timer operates.

Turns out the WDT is enabled automatically for the ESP8266. Most of the library routines reset it so you may never even know it is running unless your program hangs in a loop.

Resources

My primary resource for researching ESP8266 WDT was:

ESP8266: Watchdog functions

Hardware and Software Timers

There is a hardware WDT and a software WDT. The HW WDT is always running and will reset the MCU after about 6 seconds if the HW WDT timer is not reset.

The SW WDT seems to reset the MCU at 1.5 about seconds. You can enable/disable the SW WDT, but not the HW WDT. There seems to be little point in disabling the SW WDT as you must reset it to also reset the HW WDT.

Enabling the Software WDT

As previously mentioned, the SW WDT is running by default. If  you wish to disable it, the SW WDT is disabled with

ESP.wdtDisable();

The SW WDT is enabled with:

ESP.wdtEnable(msecs)

While you must pass a timeout value to wdtEnable, it is currently ignored.

Resetting, Kicking, Feeding the Dog

As mentioned before, most of the libraries will automatically reset the WDT for you so you should not have to do so. But if you are doing something compute bound that will take more than a second or so, you should manually reset the timer with

ESP.wdtFeed();

Summary

Unlike Teensy and Arduino, WDT is very easy to use. The downside is when the timer pops, the system just resets. You can’t jump to some code to handle the reset.

 

ESP8266 Temp Sensor Monitor Lab Notes

I recently set up an ESP8266 to monitor 3 Dallas DS18B20 temperature sensors and act as a web server to allow viewing of the data on a smart phone.

This was an experiment to try a few things:

• How can I connect to the ESP8266 via my phone to assign it to my WIFI network.
• How can I read multiple temperature sensors
• How can I provide the data via a phone readable website

This is the first time I’ve played with IoT hardware in a couple years now. I found it has become way easier to locate and install libraries and those libraries just work. In the past it was all a crap-shoot and took much more work. The resulting program for this project really just glues together libraries that do all of the hard work.

This blog post is a summary of those experiments which provided a workable little monitor:

Resources

These are the primary websites I used when developing this software:

TCP library:
https://arduino-esp8266.readthedocs.io/en/latest/esp8266wifi/readme.html

Soft Access Point Examples:
https://arduino-esp8266.readthedocs.io/en/latest/esp8266wifi/soft-access-point-examples.html

Arduino WIFI examples:
https://42bots.com/tutorials/esp8266-wifi-tutorial-arduino-ide/

Wiring Multiple Sensors to Arduino:
https://lastminuteengineers.com/multiple-ds18b20-arduino-tutorial/

Covers installing libraries into esp8266, reading a single sensor, reading multiple sensors
https://randomnerdtutorials.com/esp8266-ds18b20-temperature-sensor-web-server-with-arduino-ide/

WDT:
https://techtutorialsx.com/2017/01/21/esp8266-watchdog-functions/

Programming ESP8266 with Arduino IDE

These instructions discuss getting Arduino IDE running for ESP8266:

https://dzone.com/articles/programming-the-esp8266-with-the-arduino-ide-in-3

Once followed, I compiled and downloaded the standard blink program into my NodeMCU (0.9) to verify connectivity.

Soft Access Point Set up

My first goal was to allow configuring the ESP8266’s WIFI connection to my LAN using my phone which is now the common method for handling a device like this.

I assumed I would be writing a fair amount of code. Not true, ends up there is a library that handles the process completely. After you call, wifiManager.resetSettings, your ESP8266 is connected to the LAN. It handles everything for you.

The primary resource I used to figure this out is

https://www.digikey.com/en/maker/blogs/2017/configure-your-esp8266-wifi-module-with-new-wifi-manager

which uses the code in this git library

https://github.com/tzapu/WiFiManager

Creating a Web Server

Arduino web servers are nothing new and I’ve created a few myself. I set up a test server using instructions from

https://randomnerdtutorials.com/esp8266-web-server/

When creating the HTML for this server, at first everything was showing up small on my phone. I wanted it to fill the phone. I found the following HTML code solved that problem:

<head>
<meta name="viewport" content="width=device-width, initial-scale=1">

Further, I wanted the content to refresh every 15 seconds so I also needed:

<meta http-equiv="refresh" content=15>

Reading Multiple Dallas DS18B20 Temperature Sensors 

Once I had a running web server that was formatting, the next step was to wire up some sensors and read them.

I primarily used this resource to get the wiring correct:

https://lastminuteengineers.com/multiple-ds18b20-arduino-tutorial/

My schematic is as follows:

With the hardware configured, I then installed the OneWire and Dallas temp sensors libraries as outlined here:

Guide for DS18B20 Temperature Sensor with Arduino

Once the code for the temp sensors was running, I merged that also into the final project code.

The final code can be found here:

http://www.xyfyx.com/files/esp8266-tempSensorMonitor.zip

Raspberry PI (RPI) USB Drive Performance

Several years ago I put a USB hard drive on my development RPI using procedures like this:

https://www.raspberrypi.org/forums/viewtopic.php?f=29&t=44177

I knew it helped, but not really to what extent.

Today, I had to upgrade the RPI from Wheezy to Jessie to Stretch. My SD card based RPIs took 1 long day or two 8 hour days to do the update.

The development RPI running the USB hard drive was upgraded in 1/2 a day, if even that.  Clearly the USB system is much faster.