Installing PostgreSQL (for GnuCOBOL)

I recently wrapped up a couple of mainframe COBOL/DB2 classes at udemy.com. In general, these were refresher courses in COBOL, but I did find the EXEC SQL pre-processor very interesting.

I left the COBOL world before SQL was mainstream. When we used the computer database management system (DBMS), it was via calls like one would still do in most modern computer languages such as

CALL "DBGET" USING DBNAME, TABLENAME, ....

Once SQL came along some COBOLs (as far as I can recall HP’s COBOL/3000 was given this ability), there was a pre-processor (EXEC SQL) that allowed you to code SQL directly into COBOL such as

DISPLAY "ENTER USER-ID:".
ACCEPT USER-ID.

EXEC SQL
    SELECT USERNAME INTO :USER-NAME 
      FROM USERTABLE WHERE USERID = :USER-ID
END-EXEC.

IF SQLCODE NOT = 0, 
    DISPLAY "USER ID WAS NOT FOUND".

The mainframe COBOL/DB2 uses this pre-processor. Since there is no practical way for me to code on a mainframe (the service I looked into wanted over $300 / mo), I thought it would be neat to see if I could get a pre-processor running on gnuCOBOL. Turns out someone has written just such a pre-processor.

Getting the pre-processor running is a future topic, but one of the requirements is PostgreSQL – that is the DBMS it interfaces with.

So this blog post is my notes for getting PostgreSQL up and running.

PostgreSQL, mySQL, and SQLite

It has been years now since I used mySQL, but I did once heavily use it. I wrote an application that handled netflow data from a couple hundred routers. At the time I was pretty impressed that I was processing roughly 5 million records a day in mySQL. I liked mySQL but my knowledge of it is now 10 years out of date (jeez, how can that be??).

In my own projects for the past several years I selected SQLite as my DBMS software. I liked the licensing, and I was OK with the fact that cannot allow networked access. Plus it is very lightweight.

I believe I had looked at Postgres (as it was then called) when I looked at mySQL and mySQL looked like a much better solution then.

So how does PostgresSQL compare to mySQL and SQLite?

This article is a pretty quick comparison of the differences between the three:

https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems

A super high-level summary:

SQLite is good for IOT, or where an embedded database is necessary (my situation). It is not good for large volumes or networked access.

mySQL is popular, easy to use, and provides fast reads. Great for websites. Downside is Oracle hasn’t been keeping it current.

postgreSQL is better at data integrity and complex operations. Downside is a more complex product.

Installing PostgreSQL

While researching PostgreSQL I found this tutorial site which I found very useful for quickly getting the DBMS installed, running, setting up some test tables, and experimenting with the DBMS:

PostgreSQL Tutorial

Here are the highlights of installation:

• The installation procedure found in PostgreSQL Tutorial starts at

https://www.postgresqltutorial.com/install-postgresql/

• Download the Installer.

Installers are found at

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

I’m installing onto a test Windows box. So I selected the latest and greatest PostgreSQL version.

• Run the Installer.

Answered a few questions and 10 minutes later PostgreSQL was installed. I looked at stack builder (end of installation), but there were no other optional packages I could see using at the moment.

• Once the PostgreSQL server is running, I started pgadmin4 (in start menu) to poke around. Although browser based, this is a very nice built in query tool.

Installing Test Data

The tutorial site includes test data of a fictional DVD rental store. It includes many tables, fields, and relationships to experiment with. Besides providing example data for their tutorial, it will provide me example data down the road for gnuCOBOL.

The instructions for downloading the DVD Rental Store database is found at

https://www.postgresqltutorial.com/postgresql-sample-database/

The procedure for uploading the data into the service is at

https://www.postgresqltutorial.com/load-postgresql-sample-database/

Once the data is loaded, I used pgadmin4 to verify the tables and data exist:

Following Along with the Tutorial

At this point, you have the necessary data installed to do the PostgreSQL tutorial. I did so just to get a feeling for this DBMS.

Compared to SQLite which I know well and my fading memory of mySQL of 10 years ago, this is an amazing product. It was easy to get running and use.

Installing a Different SQL Query Tool

pgadmin4 is much nicer than the tools that come with SQLite (and what I recall coming with mySQL long ago), but I’m not a big fan of web-based software. So I went looking for a different query tool.

I ended up settling on DBeaver. It is free, has lots of features, and also works with mySQL and SQLite so it could possibly become my de facto SQL tool. In my, so far, limited use of it, I like it though it has been really slow to start up.

You can find DBeaver here

https://dbeaver.io/download/

Here is an example of output:

Conclusion

I now have a working DBMS to use with gnuCOBOL.

The Ultimate Electronics Toolbox

In the early 80’s I stumbled across a Plano 757 tackle box and purchased it to store my small assortment of electronic tools and parts. It quickly ended up at my office where I was constantly having to make cable ends for RS-232 equipment or crimp RJ-11 and RJ45 connectors for LAN patch cords.

In my opinion, it is the best electronics toolbox I’ve ever seen. The compartment layout allows for lots of little parts and some fairly long tools. I could even keep small wire cutters and needle nose pliers in the trays. On top is plenty of room for big equipment where I kept the crimpers and multimeters.

Tackle boxes generally have a bunch of trays that open when you lift the lid, or they have plastic boxes that slide in. You have to remove the box, then open the lid. This one you just slide open drawers to gain access. I loved it.

After purchasing this box, I really wanted another but either Plano stopped making them or they were very hard to come by. In the late 90’s (nearly 15 years later), I stumbled across a clearly old ‘new’ one at a mom&pop sporting goods store. I snapped it up and that became my home toolbox.

After I retired, the work toolbox became my primary toolbox and the newer one contains everything I need to go onsite and work on telephone or CATV wiring. Here is the 1980’s model still going strong:

Never saw one again until today (20+ years later). Today I was going thru amazon looking for a small tackle box for first aid supplies and I stumbled across a model 757-004 (I’m guessing revision #4). Here is the Amazon Link.

It is hard to tell if the drawer layout is the same. I tried to locate other pictures of the drawers, but this is the only picture anyone shows in their ad. My version’s lowest drawer has some bigger bins for small tools. This new box also has some bins on top of the lid.

Toolboxes, like work benches, are pretty specific to your own taste, but if you are looking around for an electronics toolbox, I’d say this one is worth a look.

 

Installing Vino (VNC) on Linux Mint 19.2

I wasted too much time figuring this out so I’ll document it here for future use and if anyone else hits the problem.

As of Linux Mint 19.2 (or maybe a little earlier), they stopped including the simple Remote Desktop support. As far as I’m concerned, idiots!! I need that on every damn system I use.

When I first hit this problem I found these instructions,

Enabling remote desktop sharing (VNC) on Linux Mint 19

These worked as far as they go, but they don’t explain how to assign a password to VNC. I must have passworded access.

Here is my full set of instructions:

• Install Vino:

sudo apt install vino

• Assuming the password you want to assign is ‘mypassword’, enter the following commands when logged into the user that will be running vino:

gsettings set org.gnome.Vino require-encryption false
gsettings set org.gnome.Vino prompt-enabled false
gsettings set org.gnome.Vino authentication-methods "['vnc']"
gsettings set org.gnome.Vino vnc-password $(echo -n 'mypassword' | base64)

• You can verify the settings with

gsettings get org.gnome.Vino require-encryption
gsettings get org.gnome.Vino prompt-enabled
gsettings get org.gnome.Vino authentication-methods
gsettings get org.gnome.Vino vnc-password

• Finally, search for the Startup Applications Preferences applet, and add

/usr/lib/vino/vino-server

• Reboot and you should be good to go!

Installing gnuCOBOL on Windows

7 years ago I did some testing with Open COBOL. I wanted to see how well it implemented COBOL and do some performance testing as well. As I recall, the implementation was lacking a bit, but I was still able to write some decent performance testing code.

I’ve been looking into COBOL again, just for fun, and decided to review the current state of the compiler. I would also like to do some performance testing and see if / how it integrates with SQLITE.

Just to make my life easier, I want to start with a Windows installation of gnuCOBOL though I may decide Linux is a better choice for my testing. So this post records my installation of the compiler.

Open COBOL is now gnuCOBOL

I didn’t look into the reasoning (legal I presume), but Open COBOL has become gnuCOBOL. From this point forward, any reference I make to COBOL is gnuCOBOL.

Locating the Compiler

See Jun 2020 update at the end of this post for a better location for Windows binaries.

gnuCOBOL is located on sourceForge.net. But I found that the default download for windows, like Linux, does not include a binary of the compiler.

Compiling projects on Linux isn’t tyically too bad, but Windows is a whole other beast. I just want a binary, I don’t want to have to build the compiler for Windows and deal with the whole Window/Linux/minGW compilation mess.

What I discovered is if you go to nightly_snapshots, there is a windows binary for gnuCOBOL 3.1.

In case that binary goes away, I have put a copy here BUT it will be as of this writing so use nightly_snapshots if you have a choice.

Note that this compilation does not include indexed file support.

Installing the Compiler

• Unzip the distribution file. I unzip into c:\gnuCOBOL and my examples will assume that location.
• In c:\gnuCOBOL\bin you will find various EXE files particularly cobc.exe and cobcrun.exe:

• In System Properties | Environment Variable you need to add the path c:\gnucobol\bin:

• In CMD at C:\, cobc.exe should work:

C:\>cobc -V
cobc (GnuCOBOL) 3.1-dev.0
Copyright (C) 2020 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <https://gnu.org/licenses/gpl.html>
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Keisuke Nishida, Roger While, Ron Norman, Simon Sobisch, Edward Hart
Built May 05 2020 16:26:39
Packaged May 05 2020 15:48:49 UTC
C version (MinGW) "5.3.0"

C:\>

Setting Up a BAT File

There are parameters you’ve got to pass to the compiler every time. It is easiest just to have a bat file. I’ll call mine COBOL.BAT and simply put it into c:\gnucobol\bin.

C:\gnuCOBOL\bin>type cobol.bat
@echo off

rem Compile a COBOL program

rem are env strings already defined? (tried using () here, but it failed so goto it is

if NOT "%COB_MAIN_DIR%" == "" goto cont

rem define env strings

set COB_MAIN_DIR=c:\gnucobol\
set COB_CONFIG_DIR=%COB_MAIN_DIR%config
set COB_COPY_DIR=%COB_MAIN_DIR%copy
set COB_CFLAGS=-I"%COB_MAIN_DIR%include" %COB_CFLAGS%
set COB_LDFLAGS=-L"%COB_MAIN_DIR%lib" %COB_LDFLAGS%
set COB_LIBRARY_PATH=%COB_MAIN_DIR%extras
set PATH=%COB_MAIN_DIR%bin;%PATH%
	
:cont

rem Start the compiler

cobc -x %*

-x indicates the output from the compiler should be an EXE file.

If you want to use the cobc command directly, simply use “cobol -V” once to set up the environment, then cobc can be used directly.

Testing

In c:\gnuCOBOL, you will find gnuCOBOL.pdf, a very comprehensive manual of the compiler.

If you want to do a hello world test, here is the code:

C:\gnuCOBOL>type hellow.cob
        id division.
        program-id. hellow.
        procedure division.
            display 'hello world!'.

Note there are 8 leading spaces on every line and another 4 for the DISPLAY verb. This is how fixed format COBOL code must be written.

Here is the compile and subsequent execution. Note that I use the -t- option during the compile. This will create a compiler output listing.

C:\gnuCOBOL>cobol -t- hellow.cob
GnuCOBOL 3.1-dev.0 hellow.cob Tue May 05 13:57:13 2020 Page 0001

LINE   PG/LN A...B............................................................

000001       id division.
000002       program-id. hellow.
000003       procedure division.
000004           display 'hello world!'.


0 warnings in compilation group
0 errors in compilation group

C:\gnuCOBOL>hellow
hello world!

Learning COBOL

If you have stumbled across this post because you want to learn COBOL here are a few resources I can give you (and I’ll try to keep this updated when I find others).

The gnuCOBOL manual is very extensive, especially for free software, but most of the time it is pretty terse.

I have found the Unisys manual to be quite well written and it goes into much more detail about the language. Granted gnuCOBOL and Unisys COBOL aren’t exactly the same, but that will probably not be much of a problem. Usually vendor manuals are pretty good about pointing out non-standard extensions.

I am towards the end of taking this udemy.com course:

https://www.udemy.com/course/mainframe-the-complete-cobol-course-from-beginner-to-expert/

This is an IBM mainframe-based course. But COBOL is COBOL. All COBOL code shown will work the exact same way in gnuCOBOL.

The list price is $175, I paid $10. For $10 it is a decent course. I would not have been very happy had I paid $175. But I get more from books than videos.

There are several books I used during my career that are, as far as I’m concerned, essential in writing production COBOL code.

The first is The Programmer’s ANSI COBOL Reference Manual, 2nd Edition by Donald A. Sordillo. This book is, of course, out of print and fairly expensive. It is $250-$800 on Amazon and no listing on ebay today. But if you want to code in COBOL, keep watching for this book at a reasonable price. It is the best I ever saw.

My college textbook (yes, I took COBOL in college), is Fundamentals of Structured COBOL Programming by Carl Feingold. You can probably find this for < $20 if you are patient. It only covers the 1974 standard, but as a college level text book it has a very thorough coverage of the language.

When I wrote the COBOL standards for the companies I worked for, I used Handbook of COBOL Techniques (Computer Partners, Inc) and COBOL with Style (Louse J. Chmura). These are great books for producing clean code that others can read.

June 2020 Update

The gnuCOBOL binary available from Source Forge doesn’t include ISAM support and for experimentation I really would like ISAM.

I messed around with building my own compiler which was successful, but it took days of messing around. Then I stumbled on Arnold Trembley’s website. He has exact instructions for building the compiler as you want it. More importantly, he keeps various flavors of the recent release available in binary form.

From his website I was able to download

GnuCOBOL 3.1-dev r3577 (16MAY2020) MinGW compiler for Windows XP/7/8/10 with COBOL ReportWriter. Includes VBISAM 2.0.1 for Indexed Sequential file access support, GMP 6.2.0, and PDCurses 4.1.0 (17.0 megabytes). Rename .7z to .exe for self-extracting archive.

Extracted this to c:\gnuCOBOL and it works perfectly, well on a hello world test anyway.

MoneyDance: First Impressions

I did a little testing of MoneyDance and I’ll share what I saw. This is by no means a ‘review’.

Installation

When I attempted to install MD, I immediately encountered problems. The most recent version is 64bit only. The system I need to run this on is 32bit (it’s a long story but there is a good reason). A little research revealed it is run in Java and Java is no longer supporting 32bit? OK….

Honestly this is a show stopper for the moment. I really want to maintain accounting on a specific machine and I’m not ready to upgrade that machine to 64bit for at 6-12 months until I know how I’m going to deal with hardware drivers.

Also, I have not been a fan of Java programs since the early days. I know Java software is better now than the early days of Java, but having to clock in/out of work on a seriously crappy Java app required by our payroll provider left a bad test in my mouth for all things Java. Nothing will piss an employee off faster than being in the office but not being able to clock-in!

Nevertheless, I really want to play with MD as it may become an option in 6-12 months. I also like the fact that it will run on linux as my laptop is now linux-based and that would allow me to do my accounting on the road w/o a VM.

Importing Data

MD did a pretty good job of importing data though certainly not perfect. First I imported my old quicken data file that has data going back to 1994. It managed to do so w/o crashing (unlike gnuCash) though there were many oddities I wasn’t sure of. So I decided to simplify and import just the past 2 years worth of data.

For the most part transactions, accounts, and categories imported fine. My biggest ‘gotcha’ was in asset accounts where I have the bad habit of depreciating back to the same account. For example, once a year I check the blue book value of my car, then I ‘depreciate’ the car by reducing the account by the appropriate amount. But instead of recording against a depreciation account, I just used the account itself. Quicken gives a warning when you do that, but it does it.

If you record transactions back to the same account like this, MD will not like it and will create a different account to record those against. So if my account is named [CAR] and I create a transaction for -10 and record it against [CAR], MD will create an account called [CARX] and record the transaction against that new account.

Once I realized what all of these ‘X’ accounts were, cleanup wasn’t that bad. PLUS I decided I really shouldn’t be taking the easy way out and I went back and properly recorded all of my depreciation against a real category .

Most of the bank/investment accounts imported with very few issues. The investment account did have quite a few bogus transaction additions. I never figured out what triggered these, but they were pretty easy to find because all of the real transactions were reconciled and the bogus ones were not so I could just delete them.

Usage

As I worked on cleaning up data I started to get used to how the MD GUI works.

I kept finding I had to use the mouse too much. I am NOT a mouse person. I expect to be able to do normal day-to-day operations with minimal mousing. I know the shortcut keys to everything I do daily. I’ll use a mouse to get an app running, then stick to the keyboard since it is far faster to use. I could navigate to some extent in MD w/o a mouse but then there were operations I just could not find a non-mouse way to handle. As I recall, some drop-down fields I would get into and could scroll with keys but couldn’t actually select the value – I’d have to click. In windows this would never happen – this was a Java problem.

As I cleaned up data I hit the MD 100 transaction limit on the trial version. I wasn’t expecting that to happen before I got a chance to test bank downloads.

As it turns out, when you hit that limitation, not only can you not add more transactions, you cannot setup bank accounts so there was no way for me to proceed with testing w/o purchasing MD.

Given the fact I knew I wasn’t going to be using it in the near future, I decided to conclude my  testing for now.

Summary

So far I would judge MD to be NOT UNACCEPTABLE to me (which is a notch down from ACCEPTABLE 🙂 ). I’ve decided to keep going with Quicken for the near term partially because of the 32bit issue and partially because MD’s interface is about as quirky as Quicken’s. So I will stick with the devil that I know for now.

 

Playing Old Interactive Games (Adventure, Warp, Mystery Mansion) Online

I made a copy of my setup of the simH HP3000 emulator available for download a couple of years ago. There are several downloads of that file every day. Evidently there are a few that really enjoy playing these old games.

I have setup online access to my emulated HP3000 so you can play the games directly through the internet rather than having to download and install the emulator. The downloadable version is still available here:

https://bigdanzblog.wordpress.com/2019/09/16/playing-old-interactive-fiction-games-on-the-hp3000/

To access the game system online, follow these instructions.

Set up Telnet Client

Telnet is the only supported protocol into this system and is not initially installed in Windows 7, 8, 0r 10.

If you are using Windows 10, follow these instructions to install Telnet:

https://www.technipages.com/windows-10-enable-telnet

If you are using Windows 7, follow these instructions to install Telnet:

https://kb.iu.edu/d/ahjz

Alternatively you can download putty:

https://putty.org/

Here is an example of configuring putty to use Telnet:

How to telnet using Putty ?

If you use putty, you will most likely need to change how the backspace key works. In putty, in the left column, select Terminal | Keyboard to bring up keyboard settings. Then change the backspace key to use control-H NOT control-?

Using Telnet to Connect to the HP3000 Emulator

You will need to bring up the CMD window

• pressing the WIN+R keys,
• typing CMD into the open box
• and then clicking OK.

At the CMD’s > prompt, type

• This will connect you to the emulator:

Connected to the HP 3000 simulator ATCD device, line 1

• Press the ENTER key repeatedly until you see a ‘:’ prompt:

Connected to the HP 3000 simulator ATCD device, line 1 

:
:

• Now type HELLO USER.GAMES:

• This will log you in and you will see a menu of games:

To play a game, enter the number of the game and press return to begin.

Additionally

There is no way to exit Trek 73 prematurely. If you want out, close the telnet window.

The emulator supports only 5 simultaneous users. If you are the 6th user you will get a connect message like this:

Connected to the HP 3000 simulator ATCD device, line 6

BUT you will not be able to get a : prompt when you press return.

I make no guarantee nor warranty regarding this emulated system. It may go down for maintenance without any notification and may disappear all together.  If it is abused, that day will be sooner rather than later.

Sep 2020 Update:

I have been requested to add the game Empire, but wasn’t having luck finding it. Recently, someone pointed out its location.

I have installed Empire onto this system.

I have never played this game. Outside of the initial setup I have no knowledge of it. If you are trying to play it and have issues, you can contact me via the email address seen when you login to the HP3000.

gnuCash and Online Banking: A Complete Failure for Me

I have several bank accounts that quicken downloads transactions for on a daily basis. Every morning, I do a sync and check transactions to make sure everything is legit. I don’t want to loose that ability.

One of my accounts uses Quicken web-connect to provide transactions. gnuCash doesn’t support those web-connect accounts at all. The other accounts use the older and more feature rich direct connect. Purportedly gnuCash supports those.

Unfortunately I was unable to get any of the direct connect accounts to work thru gnuCash’s aqbanking wizard. Most wouldn’t connect due to authorization issues. I know I have the credentials correct, it just doesn’t work.

The only account that made it past authorization then got stuck in the wizard such that I was never able to assign the bank account to  a gnuCash account.

Not being able to download transactions quickly, easily, and daily is a deal breaker for me. gnuCash is off the table.

While working on gnuCash I ran across MoneyDance, a recent finance manager software application. Like Quicken, my data is NOT stored in the “cloud”. It also supposedly supports direct connect accounts though not web connect. I can probably live w/o web-connect, so I’m going to give MoneyDance a chance next.

 

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…