rowyn: (studious)
Shout-out to Microsoft Office's Excel, the Swiss army knife of computer applications, the Spreadsheet Program That Could.

Like, whatever you want it to do.

It can.

Was this a good idea? MAYBE NOT BUT LET'S DO IT ANYWAY.

On my first exposure to spreadsheet programs, I found them confusing: "I don't understand how this works." One of my friends, Telnar, said "You know how word processors are programs for using text? Spreadsheets are word processors, but for numbers." And I thought, "Oh, that makes sense."

And Excel thought, "Cool, but WHY STOP THERE."

At some point in the 90s, Microsoft decided to watch how users actually used the various programs in the Office suite. "What kind of numbers do our users crunch in Excel?" they asked. "What sort of functions do they need?"

And the users said "Well, we don't really use this for numbers that much."

MS: "Uh. Huh. But you use it?"

Users: "OH YEAH IT'S GREAT FOR LISTS."

MS Excel developers: "That sure is a thing. So. HOW CAN WE MAKE THIS PROGRAM BETTER FOR LISTS."

Users: "Really we want to store a lot of records, like lists of our customer's names and addresses in a format that we can sort and filter and stuff."

MS Excel: "You mean like ... a database?"

Users: "What's a database?"

MS Excel: "That thing you just described. Did you know Access comes bundled with MS Office, just like Excel?"

Users: "No. Also, databases sound scary and we like you, Excel."

MS Excel: "Okay ... uh ... sure, we can pretend to be a database, I guess. I don't know if this is really a good idea ... "

Users: "WE LOVE IT THANK YOU."

MS Access: "... this was supposed to be my job, why doesn't anyone love me."

MS Excel: "WE DON'T KNOW please stop being scary so we can stop doing your job. Also, we're going to add pivot tables to give better reports on the database information that users keep putting in spreadsheets."

Users: "What's a pivot table?

MS Excel: "It gives you statistics on the data in a database. Like the total number of customers who live in Michigan and joined in 2017."

Most Users: "That sounds scary I don't think I can do that."

Jane in the back: "AW YEAH I LOVE IT."

MS Excel: "Okay, well, we already added it, so, uh, you're welcome, Jane."

Jane: *BIG THUMBS UP*

Users: "Howabout you add charts? Like pie charts, maybe, or bar graphs, or line ones perhaps?"

MS Excel: "OH YEAH LET'S JUST DO ALL OF THOSE AND ALSO TWELVE MORE KINDS IN INCREASING ORDER OF OBSCURITY."

Users: "... sure that works."

Some users: "I'd like to be able look stuff up. Like I want a formula that checks to see if Mary Watson is in my address spreadsheet and then tells me what her address is."

MS Access: "You know what is great at this? A DATABASE PROGRAM."

Some users *sidle around Access, look hopefully at Excel*

MS Excel: "We're gonna call this formula "VLOOKUP", or "HLOOKUP" if your data is stored horizontally."

Some users: "You're the best, Excel!"

John: "Oh, hey, I'd love it if I could tell Excel to do the same set of operations on a spreadsheet over and over again. Like I get this spreadsheet from my distributor every month but the columns are in the wrong order and it has a bunch of records on international sales that I don't care about, and other stuff. Anyway I do this same fifteen-step process on every file and it'd be great if I could just press a button and have Excel do all fifteen steps."

MS Access: "HAVE YOU CONSIDERED USING A DATABASE PROGRAM FOR YOUR DATABASE NEEDS."

John: *hides in terror until MS Access is gone, sneaks over to Excel, whispers* "so can you help me?"

MS Excel: "We can add that! Here, press this 'record macro' button and then do your fifteen steps, then press 'stop'. Now you can replay that macro the next time you need to do those same things on a file."

John: "Thanks!"

Most users: "What's a macro?"

MS Excel: "... we just said?"

Most users: "can we pretend it doesn't exist, it's scary?"

MS Excel: "Sure, our program works fine even if you ignore 99% of our features."

Most users: "WHEW."

Pat: "So this macro thing is great but sometimes I want to be able to tell Excel to do things automatically that are more complicated than what I can record. Like I want it to dynamically change the range of cells that it's operating on, or whatever."

MS Excel: "So, huh. You want to be able to program Excel, basically?"

Pat: "Yeah, sounds good."

MS Excel: "Okay, well, we have this programming language that records the macros, so we can just expand it and users can write programs in it."

MS Excel developers: "Are we really putting an entire programming language in our spreadsheet program?"

MS Office: "Actually, we're gonna put it in all our products."

Most Users: "TOTALLY PRETENDING THIS DOESN'T EXIST WHAT IS IT EVEN IF WE WANTED TO BE PROGRAMMERS WE WOULDN'T NEED YOUR APP."

Pat: "AW YISS PERFECT."

Robin: "Hey, Excel, can you make it so I can import stuff directly from my SQL database to a spreadsheet?"

MS Access: "WHY AM I EVEN HERE."

MS Excel: "I DON'T KNOW MAYBE IF YOU TOOK OFF THAT ZOMBIE MAKEUP AND THE VAMPIRE FANGS PEOPLE WOULD LIKE YOU ACCESS"

MS Access: *lifts vampire cloak, hisses*

MS Excel: "So, Robin, you want to do database reports in ... Excel?"

Robin: "YES."

MS Excel: "You're sure you don't want to use a SQL query builder or report writer for this instead?"

Robin: "I ONLY WANT YOU, EXCEL."

MS Excel: "... okay, we basically do anything a user has ever asked us for, so here you go."

Confession: in my 25+ years of using Excel, I have used every one of these features. I am pretty sure this means there is a WHOLE BUNCH MORE STUFF THAT EXCEL CAN DO. Does it julienne fries? PROBABLY.

Is it a good idea to build a form in Excel that uses VBA to hide and reveal parts of the form based on data entered and then send that form's data to a different spreadsheet which stores it like a database? Possibly you should have hired an actual programmer for your programming needs but EXCEL IS NONETHELESS HERE FOR YOU.

Like any Swiss army knife, Excel is not necessarily the BEST tool for any particular job, and sometimes using it for a given job turns out to be a TERRIBLE MISTAKE. But I love you anyway, Excel. You're the best. ❤️
rowyn: (Default)
I still consider myself "on break", so I've been doing whatever I feel like doing in my free time instead of pushing myself to be productive. I still do productive things, but I stop once I stop feeling like it. It's been pretty relaxing, and not very productive.

I've written 2500 words on The Twilight Etherium in the last week, which by my current standards is much the same as "not writing at all". I looked up my the old Master Plan(tm) I used while writing Prophecy, and realized that 2500 words a week beats my average for the entire time I was working on that manuscript. For those of you not following me back when I was working on Prophecy, I complained pretty much nonstop about how hard it was to write enough to meet my goals.

Lest you think that this means "well, obviously it's easier to write for fun than it is to meet a goal": my goal last month was 11,700 words per week, and I had no particular difficulty making it. Beyond that, after 2004 I stopped setting any rigid writing goals until 2012. I wrote less from 2005 through 2011 than I had under the Master Plan(tm). I didn't pick up writing speed until I started writing fantasy romance in 2012. (My first foray into fantasy romance was a joint project with LadyPeregrine in 2012, which alas remains unfinished. But it did clarify my love for the subgenre and contributed to my decision to write A Rational Arrangement in 2013.)

Anyway, 2018 Rowyn is much better at working on books than 2003 Rowyn.

~

On Thursday 11/29, I talked to an artist on Twitter about commissioning them for a cover for The Princess, Her Dragon, and Their Prince. They tentatively agreed, and I said I would send character descriptions and a contract and such in the next couple of days. This was one of only two writing-related tasks for the month.

Doing it required looking through the manuscript for The Princess, Her Dragon, and Their Prince, something I had not done -- well, at all. For many years, I used to read my works-in-progress avidly. This has some good effects: it keeps what I've already written fresh in my mind and I get in some preliminary proofreading. But I mostly did it because I like reading my own work. In recent years, I've been more inclined to avoid reading my own works until I start editing them. This also has good effects: it means I am less familiar with the text when I finally edit it, so I am more likely to see errors, and it means I'm not avoiding writing on the pretext that reading the existing book is productive. Princess is one of the books where I didn't go back to read earlier scenes of the book once I finished writing them.

Once I started looking at it to extract info for my artist, I got caught up in reading it. After I got to the end, I decided to start the very early part of the editing process. This is where I make a spreadsheet of all the things I want to change in the text. This part is easy because I make notes as I go along about what I want to change, so I just have to search for square brackets in the master doc and put the items on a spreadsheet.

The list had a total of 39 items. I each item scored based on how annoying it will be to do. Most of them are in the "pretty annoying" range: 25 items with a rating of 6 or higher, on a scale of 1 to 10. I went on to complete the four easiest items on the list. I don't like editing so I generally work the easiest items first so that the list will get shorter and less intimidating. Anyway, I have 35 items left to do. The total number of items on the list generally grows as I edit. It's just as well I don't plan to release this book until March at the earliest.

~

One of the sections on the annual self-evaluation form at work is "what are your long-term career goals?" Back in 2016, I looked at this question and decided to put down the truth: "retirement". I talked to my boss about it: "I would like to drop to reduced full-time -- 4 days a week -- in the next year, and drop to part time -- 3 or 2.5 days a week -- in the next two years."

To my surprise, my boss was supportive of this: "I would love for you to stay full time, but if you need to reduce your hours, we will definitely accommodate you. We value your expertise and want to keep you in whatever capacity."

When Lut was diagnosed with cancer in 2017, this had two opposed effects on my retirement plans. On the one hand, money became much more of a concern, as my expenses went up with car ownership and incidentals. It also became critical that I keep Lut on my health insurance. On the other hand, managing Lut's care was exhausting and I desperately wanted more time in which to do so. In September of 2017, the latter concern triumphed, and I dropped to four days a week. My "day off" was used to take Lut to whatever doctor visits he needed.

For a while, this sufficed. But over the last couple of months, I've felt more and more like four consecutive work days is too many, despite the fact that Lut's doctor visits have dropped to one or two per month. It had reached the point where I used PTO to cover the days when I had to take Lut to the doctor, rather than switching my day off to that day.

Last week, my boss told me that she had applied for a new managerial position in a new department, and so was leaving her current role. The promotion was official on 11/26, but she's still working in our department for a couple of weeks while the department manager searches for her replacement. My boss will officially transition in January, whether or not a replacement has been hired. Our team have all been in our roles for years, we are all pretty well cross-trained, and we can work autopilot for months without an intermediary supervisor between us and the department head, if necessary.

But this news made me think harder about transitioning to part time. My boss and the department manager have been big boosters for me for as long as I've been working for Teenage Bank. The replacement boss will not be someone within my department and I probably won't know them. Would they still support my switch to part time, as my boss had committed to?

So I talked to my boss about it: "Would it be better to switch now, while you're still here, or should I wait and see who your replacement is, or possibly stick the department manager with handling it if she's not able to replace you for some months?"

Boss: "If you want to do it soon, best to do it now, while I'm still here to manage the paperwork,"

So I emailed her a formal request to drop to part time starting January 7. I decided to wait so I could get the 6 hours of holiday pay for Christmas and New Year's that reduced-full-time employees get, instead of the 4 hours that part time employees get. I considered waiting until after MLK Day too, but three more four-day weeks felt like a lot as it was.

My overall feeling is relief at finally pulling the trigger. I have about six months of expenses in my accounts in the bank, and a lot of money in retirement accounts. The cut in my weekly paycheck will be much larger, proportionately, than the 25-33% indicated by dropping from 30-32 hours to 20-24 hours per week. First, my personal insurance premiums go up by a lot: the bank pays 80% of the insurance premium for full time employees and 0% of the premium for part-time. Second, a lot of my paycheck goes to repaying the 401(k) loan for my car, and that amount will remain fixed. I'm not sure how much I will be making, but it will not be much.

My writing income is not going to make up the difference. My net income from writing averages to maybe $200 a month, before taxes.

But Lut will still have his disability stipend from Social Security, and the house is paid for. I am hoping we can manage without resorting to withdrawing money from my retirement accounts, but if I have to withdraw money from my retirement accounts, well, that's why have retirement accounts.

And I am looking forward to spending less time at my day job. Only two 4-day weeks left!
rowyn: (current)

So.

I open Foo.dbq in Showcase Query on my local computer.
I open the exact same Foo.dbq, from the exact same directory, on my remote computer.

I look at the properties for variable DATE1 on my local computer. It is "Range of Values (BETWEEN)"

I look at the properties for variable DATE1 on my remote computer. It is "Single Value".

...

WHAT

IT IS THE SAME FILE I HAVE NOT CHANGED ANYTHING WHY

Does Showcase Query have an option for "Make all variable types into Single Value on file open"? Why would anyone ever code a program to do this?  D::

rowyn: (hmm)


One of the oddities of working at a large bank is that, since I am doing reports that are viewed by far more people, I get a lot more questions about them. Naturally, I also get more of the same questions, over and over again, from different people.

 

And then there are the mystifying questions.

 

Loan Officer: "Why is this loan on my past due list? It's not matured!"
Me: "... because it's past due? Just like the other past-due-but-not-matured loan on your list of three past due loans?"

 

Loan Officer Assistant: "Why isn't Loan 123 on the matured loan list? It's matured! Oh, and so is 456. Why aren't they there?"
Me: " ... um. They are on the matured loan list. Right where they are supposed to be?"

 

I got nothin'.


rowyn: (hmm)
I wrote this out in the hopes that I'd figure out a solution once I had it laid before me, but nooooo. So I'm turning to my much smarter readers for advice. /o.o\

I need to do a report showing all loans to a given customer, and all guarantors on those loans. The tools that I have for accomplishing this task are Crystal Reports and Excel 2003. 

This information is stored in an SQL database, in a format where Customer is linked to Loans and Loans are linked to Guarantors.  So, for example:

Foo Inc has loan #s 100, 101, 102 and 103.  Loan #100 is guaranteed by Fi Foo and Fum Foo. Loan #101 is guaranteed by Fi Foo, Fum Foo, and Fi Fum Enterprises. Loan #s 102 & 103 hav no guarantors at all.

Now, I can query my database using Crystal Reports and get the following table:

Borrower Loan Number Guarantor
Foo Inc 100 Fi Foo
Foo Inc 100 Fum Foo
Foo Inc 101 Fi Foo
Foo Inc 101 Fum Foo
Foo Inc 101 Fi Fum Enterprises
Foo Inc 102 --
Foo Inc 103 --


But I actually don’t care which loans are guaranteed by which individuals.  All I want is each unique loan number and each unique guarantor.  So what I want is more like:

Borrower Loan Number Guarantor
Foo Inc 100 Fi Foo
Foo Inc 101 Fum Foo
Foo Inc 102 Fi Fum Enterprises
Foo Inc 103 --


Or, perhaps less confusing:

Borrower Loan Number Guarantor
Foo Inc 100 Fi Foo
101 Fum Foo
102 Fi Fum Enterprises
103


Aaaaand …

I can’t figure out how to do this.  >.<

If I wanted to show all the guarantors and didn’t care if I showed all the loans or not, I could do that by grouping on the guarantors and hiding the details (where all the loans would be hidden). Or vice-versa, if I wanted to show all the loans and didn’t care if I showed all the guarantors or not.  But I need to show the unique results for each, and I need to do this for 100+ borrowers, who may have only one loan and no guarantors, or might have 20 loans with eight guarantors each.  Showing only unique instances would be the difference between a report that fits on 10 pages and one that takes up 200.

I don’t want to do this by hand, and I don’t want the people who are currently doing it manually keep doing it manually, because that is suckage beyond words. Gah.

RTFM

Apr. 14th, 2009 04:25 pm
rowyn: (Default)
So, yesterday, I combed through the help files for our banking software, looking for information on the changes made to a report in the latest version. After several hours of RTFM, and locating some wonderfully detailed information on the report that did not include the recent changes, I logged a call to their support saying "I can't find this information in the help files. Would you please let me know X, Y and Z?"

Today, I spent four and a half hours in a meeting. Four. And a half. HOURS.

When I got out, I saw I had email regarding my support call. I checked it. The response?

"RTFM."*

So that's been my day. How was yours?

* No, the support response said nothing about where to find it, or give any indication that the person who wrote it had taken any action to verify that the information she was claiming was there was, in fact, there.

I Are Dumb

Nov. 13th, 2008 04:53 pm
rowyn: (downcast)
Or maybe it's Crystal Reports, not me.

I run into this problem now and again, where I need to do a report on a database field that might or might not have actual data in it.

As an example, starting with a report that shows the value of Funfield in all records:

Name Funfield
Boo Foo
Goo Ooo
Joo Due
Noo

Now I modify this report to show all records where Funfield = Foo.

Name Funfield
Boo Foo

Next, show all records where Funfield != Foo.

Name Funfield
Goo Ooo
Joo Due

Why isn't Noo on this report?

If I try isnull({funfield}), I get no results:

Name Funfield

Now, say I do a formula named "NoFun" and define it as "if {funfield} = Foo then true, else false" and display it on my original all-results report, I get:

Name Funfield Nofun
Boo Foo True
Goo Ooo False
Joo Due False
Noo False

But if I try to get sneaky and ask for all results where Nofun is False:

Name Funfield Nofun
Goo Ooo False
Joo Due False

... Noo's still missing.

Anyone out there smarter than me that knows (a) why this happens and/or (b) how I can get around this? I'm using Crystal v.11.

... and preferably short of redoing the report in Excel, because it's not this simple a report. ;_;
rowyn: (thoughtful)
My bank recently started using a software product called "ImageNow".

Technically, the bank's been using it since before the merger in 2001. But until last year, no one at the bank had any training on it and it hadn't been set up by anyone who understood what it could do. So it was being used, but very, very badly.

ImageNow is marketed as a virtual file storage system. You scan your documents in, and then ImageNow can organize them to mimick your existing physical filing system. In theory, you can throw out your physical files and just use ImageNow for everything.

Except it's actually better than that. ImageNow doesn't just store image files; it can store and display just about any document type. It has 5 customizable property fields, plus fields for "Drawer" and "Document Type", which are mainly useful for security purposes. It basically gives you a little database of information attached to every document you put into it. One of its big selling points is that it's very easy to set it up so it "talks" to whatever database system you're already using, and it can pull the data from that program and attach it to the document you're putting into it, so you don't have to do data entry for every file.

One of my first thoughts, after training on this software, was "I want to scan my desk and put it into ImageNow". I hate digging through piles of paper looking for one piece. I want my computer to find it for me. We didn't buy ImageNow to let people organize their desks: we bought it to store the documents associated with customer accounts. But there's no reason it can't store the paper at my desk, too.

So I persuaded my boss to let me set up my own drawer, and I've been dumping this and that into it.

And the thing that struck me, as I was dumping files from my desktop into it, is that ImageNow isn't letting me do anything I couldn't have done two years ago when we replaced the department copier with a copier/scanner. I could have scanned everything then and saved it into directories and files on my computer.

Except for one thing: the directory tree architecture sucks for organizing things. Here we are, in the 21st century, and so far as I can tell every computer operating system from Windows to Macs to Linux to the Internet is still based on directory trees. What's up with that? ImageNow's system doesn't feel revolutionary or perfect, by any means. But it sure feels a lot better than C:\Toddler Bank Loans\Customer Number\Customer Name\Account Number\Document Type\Doc Date\. (Which, perhaps, is something like what ImageNow looks like on the backend that I never see. Or maybe it looks more cryptic than that and the searchable database is stored in some attached file). But in ImageNow I can, for example, do a search for name = X AND document type = Y. At the same time! It's not Google, but I've found that even Google isn't that good at, say, helping me find specific emails in Gmail. If I don't remember the right keywords it can't figure them out for me. It's kind of handy to have something prompting me to put in keywords.

I don't want to undersell ImageNow, which does more than just offer a more searchable file storage method. But I do find myself wondering "why can't my operating system's file storage work like this?" How hard would it be to make an applet that laid over Explorer and prompted me for customized key words when saving files, so that I could search for those later instead of trying to remember "What did I name this file?"

February 2019

S M T W T F S
      12
3456789
10111213141516
17181920212223
2425262728  

Syndicate

RSS Atom

Most Popular Tags

Style Credit

Expand Cut Tags

No cut tags
Page generated Feb. 18th, 2019 04:07 am
Powered by Dreamwidth Studios