Microsoft Excel

[CtrlAltDel]

Or just do =INT(A2).   

Yeah, dates are stored as integers and times as fractional parts of a day, so, using INT(A2) will give you what you want. If you want just the time, use A2 − INT(A2), since there's no FRAC() function.

[hotdogPi]

since there's no FRAC() function

You can just take it modulo 1.

[CtrlAltDel]

since there's no FRAC() function

You can just take it modulo 1.

That's clever. Thanks.

[J N Winkler]

I have trained myself to use both Excel and Access.  While I wouldn't consider myself an expert by any means, I'd say they are straightforward for someone accustomed to thinking of data in tabular format.

What seems to me to be de-emphasized, if not missing, in the preceding discussion of importing/manipulating/exporting data in CSV (i.e., a pure text-based format) for tasks that need to be performed periodically is consideration of how the workflow can be structured so that a given task can be handled noninteractively from start to finish.  Excel's apparently dysfunctional parsing behavior, and the possibility of using Access as an alternative, don't come into play when it is a program or script that handles the importing, parsing, calculation/analysis, and export from start to finish.

Most of my downloader scripts are designed to do this in one fashion or another--go to this web server, identify available documentation packages, determine which ones haven't already been downloaded before, download them, and log them so they aren't downloaded again in the future.  So are a few selector scripts (e.g., find the 10% or so of Ohio DOT, PennDOT, and Michigan DOT contracts that have signing quantities) and signing sheet extractor scripts (e.g., find the sheets in a PDF plans set that have keywords associated with signing and extract them).

[Scott5114]

One thing about people saying to use a proper database instead...

My Excel files are not just export data:  they're also import data.  For example, I export data from an MSO's live online database (accessed via VPN) in Excel format, then I do all my data manipulation with sorting and formulas and pivot tables and all that jazz.  But then I take the final product and upload it into our own company's payroll WMS (created in-house).  So, the data initially comes to me in Excel format, and then I need it to be in Excel format at the end of the process as well.

In an ideal world, those should be SQL files at both ends of the process, and (depending on the type of data manipulation you do) the manipulation could probably be done more efficiently in SQL as well.

Is SQL easy? Not really. But then again, once you get to the point you're pivoting tables and having to figure out odd edge behaviors like what the integer function does to dates, you're not doing anything easy in Excel either.

[kphoger]

One thing about people saying to use a proper database instead...

My Excel files are not just export data:  they're also import data.  For example, I export data from an MSO's live online database (accessed via VPN) in Excel format, then I do all my data manipulation with sorting and formulas and pivot tables and all that jazz.  But then I take the final product and upload it into our own company's payroll WMS (created in-house).  So, the data initially comes to me in Excel format, and then I need it to be in Excel format at the end of the process as well.

In an ideal world, those should be SQL files at both ends of the process, and (depending on the type of data manipulation you do) the manipulation could probably be done more efficiently in SQL as well.

Is SQL easy? Not really. But then again, once you get to the point you're pivoting tables and having to figure out odd edge behaviors like what the integer function does to dates, you're not doing anything easy in Excel either.

1.  My process has a LOT of steps.  But I do the same steps every day, so I hardly have to think about them anymore.

2.  If I really felt like it, I could probably condense a lot of them into a macro.

[Scott5114]

1.  My process has a LOT of steps.  But I do the same steps every day, so I hardly have to think about them anymore.

2.  If I really felt like it, I could probably condense a lot of them into a macro.

This does seem like it could be an ideal candidate for automation (though the rest of my post discusses caveats to that). A macro would probably save you a lot of time and effort, though generally I eschew in-program macros as much as possible in favor of general-purpose scripting languages like Perl and Python. Perl is less likely to break my scripts through an update (and if it does I can specify a particular version number for it to use), and it avoids tying me to a specific spreadsheet package because I have a macro library based on it. (Of course, in a corporate environment, "relationship ended with X software package, now Y software package is my best friend" is an additional occupational hazard of macro usage.)

My current "boss" has extensive experience as a Perl programmer himself and has a mantra that if we find ourselves doing the same thing more than three times we should raise the possibility of scripting it. This has led to things like the preparation of thumbnail images in Photoshop, a somewhat straightforward task that usually took ten minutes or so, getting collapsed down to merely typing 6 letters into the command line and verifying the result is correct.

What seems to me to be de-emphasized, if not missing, in the preceding discussion of importing/manipulating/exporting data in CSV (i.e., a pure text-based format) for tasks that need to be performed periodically is consideration of how the workflow can be structured so that a given task can be handled noninteractively from start to finish.  Excel's apparently dysfunctional parsing behavior, and the possibility of using Access as an alternative, don't come into play when it is a program or script that handles the importing, parsing, calculation/analysis, and export from start to finish.

This is another excellent point–it is far easier to deal with data fresh from a database programmatically, rather than parsing a spreadsheet file and hoping to recreate the behavior of any formulas found therein. Most modern programming languages have modules in the standard distribution for querying a database file and returning the result set in whatever native data structure (such as an array or hash/dictionary) makes the most sense for that language.

Unfortunately, the concept of scripting is so de-emphasized on Windows as to be nonexistent by default in anything other than the form of batch scripting. This means that in situations where there's a corporate IT overseeing the environment and denying an ordinary user from installing software of their choice, including a script interpreter, slapping together a script to automate a repetitive task is simply not an option. And most computer users just...don't care to learn how to automate tasks, because it is rare that a business's culture encourages it, despite the fact that it could save them many man-hours on labor that doesn't need to be done by a human and free them up to do things that are more important.

Which is kind of a shame for business productivity. I took pity on a friend of mine once, wrote her a Perl script, and gave her instructions for how to smuggle it onto an OS X machine and run it. The script allowed her to accomplish a year's worth of work in a single day.

[kphoger]

I've avoided macros thus far because I don't want the process to become so automated that I forget how to do it manually–and then the format of the export data change such that I have to do it manually.

[Scott5114]

I've avoided macros thus far because I don't want the process to become so automated that I forget how to do it manually–and then the format of the export data change such that I have to do it manually.

Couldn't you just read the macro code at that point to refresh your memory as to what it was doing?

If the problem is "what if I forget how to read the code I wrote"–couldn't you just use comments to document it for your later usage? (Which is what a good programmer should do anyway.)

[kphoger]

I've avoided macros thus far because I don't want the process to become so automated that I forget how to do it manually–and then the format of the export data change such that I have to do it manually.

Couldn't you just read the macro code at that point to refresh your memory as to what it was doing?

If the problem is "what if I forget how to read the code I wrote"–couldn't you just use comments to document it for your later usage? (Which is what a good programmer should do anyway.)

Yes.

Another problem is that one or two of the steps would likely need to be changed at least once a year (for various reasons having to do with how contractors get paid by an MSO).  I'd rather use muscle memory every day and also be thoroughly engaged in the process than have to periodically re-record a dozens-step-long macro.

[Scott5114]

I've avoided macros thus far because I don't want the process to become so automated that I forget how to do it manually–and then the format of the export data change such that I have to do it manually.

Couldn't you just read the macro code at that point to refresh your memory as to what it was doing?

If the problem is "what if I forget how to read the code I wrote"–couldn't you just use comments to document it for your later usage? (Which is what a good programmer should do anyway.)

Yes.

Another problem is that one or two of the steps would likely need to be changed at least once a year (for various reasons having to do with how contractors get paid by an MSO).  I'd rather use muscle memory every day and also be thoroughly engaged in the process than have to periodically re-record a dozens-step-long macro.

Microsoft macros are actually programs written in a scripting language called Visual Basic for Applications (VBA). It is baked into both Excel and Word (and probably PowerPoint too, though I don't remember). This was the scripting language of choice for 13-year-old me when I wanted to screw around in computer class and actually do something interesting after I'd already done the assignment of "type this example business letter into Word and format it". It is a full-on scripting language that is basically a pared-down version of the full Visual Basic.NET programming language that can be used to create full-fledged Windows programs. With VBA, you can even create dialog boxes with fully functional buttons, text boxes, check boxes, and so on (not that that's too useful for most Excel macros, but it makes 13-year-old kids who hadn't yet convinced their mom to buy them a copy of Visual Basic 6 on eBay feel like they're hot shit).

When you record a macro, Excel is actually translating what you do to VBA code and saving it. You can pop this macro open in the in-program code editor and inspect and change it to your liking. You can also write macros from scratch without using the record function. So rather than re-recording the macro, when the process changes, you merely have to open the code editor and change the relevant code. (This would be easier if you wrote it from scratch than if you used the record function, because to get it to work you would already be familiar with what each of the functions you're invoking do.)

[kkt]

I've avoided macros thus far because I don't want the process to become so automated that I forget how to do it manually–and then the format of the export data change such that I have to do it manually.

Couldn't you just read the macro code at that point to refresh your memory as to what it was doing?

If the problem is "what if I forget how to read the code I wrote"–couldn't you just use comments to document it for your later usage? (Which is what a good programmer should do anyway.)

Ideally one is writing code for one's workplace, not for oneself.  The problem isn't "what if I suddenly forget what my code does", the problem is "what if I am hit by a truck crossing the street and my successor has to figure out what's going on".

[kphoger]

Ideally one is writing code for one's workplace, not for oneself.  The problem isn't "what if I suddenly forget what my code does", the problem is "what if I am hit by a truck crossing the street and my successor has to figure out what's going on".

Well, in my case, the answer to that question is "they're screwed".  My co-worker (yes, one co-worker and no one else) can cover my daily duties while I'm out of the office, but nobody but me knows how to do my weekly, bi-weekly, and monthly tasks.

I started writing up SOPs, but it gets tedious after a dozen pages of step-by-step Excel functions.

[kkt]

Many times what really happens is not ideal.

[Scott5114]

Ideally one is writing code for one's workplace, not for oneself.  The problem isn't "what if I suddenly forget what my code does", the problem is "what if I am hit by a truck crossing the street and my successor has to figure out what's going on".

Well, in my case, the answer to that question is "they're screwed".  My co-worker (yes, one co-worker and no one else) can cover my daily duties while I'm out of the office, but nobody but me knows how to do my weekly, bi-weekly, and monthly tasks.

I started writing up SOPs, but it gets tedious after a dozen pages of step-by-step Excel functions.

What's really funny is when you have someone like you and management either fires them for some frivolous reason, or else pisses them off enough that they quit with no notice. Then the whole department finds out in excruciating detail exactly how much smoother things were having that person around.

[Dirt Roads]

subthread "A"

What seems to me to be de-emphasized, if not missing, in the preceding discussion of importing/manipulating/exporting data in CSV (i.e., a pure text-based format) for tasks that need to be performed periodically is consideration of how the workflow can be structured so that a given task can be handled noninteractively from start to finish.  Excel's apparently dysfunctional parsing behavior, and the possibility of using Access as an alternative, don't come into play when it is a program or script that handles the importing, parsing, calculation/analysis, and export from start to finish.

This is another excellent point–it is far easier to deal with data fresh from a database programmatically, rather than parsing a spreadsheet file and hoping to recreate the behavior of any formulas found therein. Most modern programming languages have modules in the standard distribution for querying a database file and returning the result set in whatever native data structure (such as an array or hash/dictionary) makes the most sense for that language.

Unfortunately, the concept of scripting is so de-emphasized on Windows as to be nonexistent by default in anything other than the form of batch scripting. This means that in situations where there's a corporate IT overseeing the environment and denying an ordinary user from installing software of their choice, including a script interpreter, slapping together a script to automate a repetitive task is simply not an option. And most computer users just...don't care to learn how to automate tasks, because it is rare that a business's culture encourages it, despite the fact that it could save them many man-hours on labor that doesn't need to be done by a human and free them up to do things that are more important.

Which is kind of a shame for business productivity. I took pity on a friend of mine once, wrote her a Perl script, and gave her instructions for how to smuggle it onto an OS X machine and run it. The script allowed her to accomplish a year's worth of work in a single day.

subthread "B"

Ideally one is writing code for one's workplace, not for oneself.  The problem isn't "what if I suddenly forget what my code does", the problem is "what if I am hit by a truck crossing the street and my successor has to figure out what's going on".

Well, in my case, the answer to that question is "they're screwed".  My co-worker (yes, one co-worker and no one else) can cover my daily duties while I'm out of the office, but nobody but me knows how to do my weekly, bi-weekly, and monthly tasks.

I started writing up SOPs, but it gets tedious after a dozen pages of step-by-step Excel functions.

What's really funny is when you have someone like you and management either fires them for some frivolous reason, or else pisses them off enough that they quit with no notice. Then the whole department finds out in excruciating detail exactly how much smoother things were having that person around.

I was planning on responding to subthread "A" earlier, and now subthread "B" shows up.  These themes were closely related at my employer.  We had a situation where the firm developed simulation software for its bread-and-butter work and then won a project in another market that was keenly interested in that particularly type of analytical process for their market.  Then I got hired with background in three of those four areas.  Oddly, this project required me to develop simulation software for their market (ergo, that fourth area that I didn't have experience).  We were extremely successful with the rapid development phase, but things got tough when trying to standardize the software into a one-size-fits-all platform. 

Anyhow, we tackled a project that was too complicated for the one-size-fits-all platform and the other programmer couldn't keep up with the software changes for the project.  We were still in the DOS days, and had to hand-crank the simulations using Lotus 1-2-3.  There were all sorts of crazy issues trying to pass data back-and-forth between databases, the modeling input tables, the modeling output tables and the simulation spreadsheets.  Pretty much all the issues in subthread "A" and subthread "B" occurred on that project. 

[kphoger]

Putting the serial number in quotes in the CSV makes it interpreted as text, right?

It helps to convert your cells to text format before pasting-as-values into them.  But that doesn't help when the data I get from outside the company has already been number-format-ruined.

New frustration!

My job title is 'Audit', and the bulk of my job description is twofold:

  1.  To make sure field techs are billing our company correctly for the work they perform, because we don't want to pay them for more than they're entitled to.  Techs are not paid hourly, but rather based on what sort of work is required for each job (piecework).

  2.  To make sure the MSOs we work for are paying us correctly for the work our field techs perform, because we don't want them to pay us less than we're entitled to.

Part of this process involves tracking chargebacks for things like damages, lost equipment, failed QCs, etc.  When it comes to lost cable/internet/phone/security CPE (customer premise equipment–such as a rental modem or DVR box), I keep a running list of chargebacks, just in case we ever end up getting charged twice for the same item.  This could happen if the item were later found, recycled through the system, reissued to our company, and then subsequently lost again later.  The other day, I thought I had my first case of that happening.

I have the serial number column in Excel formatted to highlight duplicate values, and I had one pop up red the other day.  And I was all prepared to dispute the charges–about ready, in fact, to hit 'Send' on my e-mail to alert the MSO of my dispute.  But then I noticed something...

This particular model of modem uses an 18-digit all-numeric serial number.  Because Excel likes to convert such numbers into scientific notation and lop off significant digits, I have to format cells as text–which I do before pasting-as-values into the cell.  But apparently that's not quite how things work in Excel.

You see, even though the cells are supposedly formatted as text, and even though each cell correctly displays all 18 digits of the serial number, Excel nevertheless apparently doesn't bother to read all of the digits when checking for duplicate values.  As it turns out, the two serial numbers on my list–that Excel determined were duplicates–were only identical to the first 15 digits.

Specifically, they were these two numbers:
212930061077217513
212930061077217161

When the data came over from the MSO, they were each treated as numbers.  The cell displayed '2.1293E+17', and the formula bar showed a stored value of '212930061077217000' (doing away with the last three significant digits).  However, on my own running list files, they are formatted as text and show the full number string in both display and formula bar.

I just barely caught this error in time to not dispute the chargeback.

[Scott5114]

Geez. That's a hell of a showstopper there.

I wonder if you might be better served by storing that data in some sort of database. Microsoft Access would work if you have access (snrk) to it. Personally, I keep track of that kind of thing in DB Browser for SQLite, but it is probably just a hair technical for most users, so if you have to share that data with someone else it may not be the best choice.

I am so distrustful anymore of programs mangling important data that I'll save it in a .txt file if it's not too complicated. Lately I've gotten in the habit of using JSON for anything that looks like a series of key/value pairs:

Code Select Expand


{
   "invoice date": "2022-10-14",
   "market": "TUL",
   "tech_nbr": 71336,
   "serial_number": "212930061077217513", # quotes force this to be parsed as a string
   "moved_to_lost_status": "2022-10-07"
}
{
   "invoice date": "2022-10-14",
   "market": "TUL",
   "tech_nbr": 71336,
   "serial_number": "M11639TRO692",
   "moved_to_lost_status": "2022-10-07"
}


The benefit of this, of course, is that it works on every computer under the sun with no special software, just Notepad. It can be easily searched with the same. If you have access to a scripting facility, it is very easy to parse and do something interesting with (most scripting languages have a prebuilt JSON parser these days). The downside is that it is pretty tedious to add large amounts of data to by hand.

[kphoger]

You lost me at Access.   

Well, that and the three or four terms I don't even understand...

[Scott5114]

Maybe a picture will help.


This database, which contains all of the road signs I've drawn for work, may as well be one of my internal organs at this point. (Portions pixelated to protect the innocent.) It works more or less just like a spreadsheet, except for the fact that there's no formatting, no formulas, and data types can only be declared on a per-column, rather than per-cell, basis. (Unlike some database programs, SQLite is more like a spreadsheet in that it doesn't really care if you put text in a number field or vice-versa. It will just by default treat data as what type you told it it was. So my "invoice" column there is a text column, so even though I am entering numeric data in there, it's treating it as text, thus why it's left-aligned. What it does not do is force the data into the column's format; I couldn't take that "244" on line 756 and do math to it, because it's in text format.) You can also tell it you want a column to only contain unique values, and it will pop up an error message if you try to enter a duplicate.

If I want to filter the data, those filter boxes up top do that. So if I want to see only, say, signs from Minnesota, I can put "MN" in the state filter and it will show only the MN designs. If I want to filter it further and show only completed designs from Minnesota, I can add "1" to the "completed" column and it will show only rows where state = "MN" and completed = "1".

If I want to get more advanced, and I have a really complicated query I want to do, I can go to the "Execute SQL" tab there and write an expression in Structured Query Language that will get me the precise data I want. (Or modify it, even. If, say, Texas seceded from the union I would be able to write a SQL statement that would take all instances where state = TX and change it to country = "TEX" and state = NULL, by writing one statement, rather than manually changing every row.)

[J N Winkler]

You lost me at Access.   

Well, that and the three or four terms I don't even understand...

I'd just add to what Scott is saying by noting that you are trying to perform blockout:  in this case each object (lost or damaged piece of equipment) is blocked out by an unique identifier (serial number) so that the associated operation (reimbursement) is performed only once.

This is literally what I do almost every time I write a downloader to keep me up to date on a given agency's highway construction plans--I look for an unique identifier for each plans set (or documentation package), and then design the downloader to look for it, log it when it downloads the corresponding file, and filter all new prospective downloads by the log to ensure nothing is downloaded twice.  Sometimes the identifier is the URL, sometimes a filename, sometimes a database key number, and sometimes a combination of these and other parameters.

There are many, many ways to do it, but it nearly always is easier when the information you need is in plain text format and is organized in a structured way.  It doesn't have to be JSON, XML, or CSV--as long as it's plain text, find and findstr (NT batch) or grep and awk (Unix) can handle it at the command line or within batch files (NT batch) or shell scripts (Unix).  If you structure the information such that each object is on its own line in a text file, then you can go line-by-line using for loops of various types.

[kphoger]

Even if I thought I were capable of learning all this technobabble you guys are saying...  at this point in my life, suggesting I switch away from Excel is akin to suggesting I divorce my wife just because she elbows me in the face every so often when rolling over in bed.

(No, my wife doesn't elbow me in bed.)

[jemacedo9]

I find that if I want to convert numbers to text, especially when Excel wants to default to scientific notation, just formatting the cell doesn't work. Neither does format tjen copy/paste values.

Instead, highlight the column, then do a Data : Text to Columns step.  Most times this is used to split data from one column to multiple columns.  But in this case, you're not going to split.  In the first step, select Fixed Width, then in the second step don't add any columm breaks, and in the third step,you can select Text instead of Number, and that actually converts the value to a text value within the same column.

[skluth]

I'm like kphoger. I used Excel a lot before I retired. It was a handy tool. I used it for calculations. I used it as a quick-and-dirty database at times. I used it to organize requirements for developmental projects. It does a lot of things I'll never use like macros, functions, and pretty graphs. It's like the other Office programs that I use, Word and PowerPoint. They have a zillion capabilities I'll never need and I hate them all but find them all too useful to give up, even in retirement. At least Office is reasonably priced at $70/year these days, which I find is worth it rather than rely on the free Sun Office.

[Rothman]

I don't understand not using functions with Excel.  They take tasks that take an hour or more and reduce the time down to minutes you can count on one hand.

I've used macros in Excel by just copying code of the Internet ("Oh, that looks like something I'm trying to do...").

I've used Access for real database work and data organization, but I invariably run a query through Access' interface (sometimes modifying SQL directly a little -- I have very limited knowledge) and export to Excel.

Ah, JSON.  I've only used that to view code from the Internet a few times.