Recognizing 2-digit years

[Dallman Ross]

Today I found to my consternation that my broker's updated web pages
have changed the format of the CSV files I download daily. I have
to re-work some complex formulas. On my way to accomplishing that,
I find one change is that what had been four-digit years are now
two-digit years. My Excel 2002 under XP isn't recognizing them as
dates. Is there an easy fix here?

Thanks,
Dallman

 

[Niek Otten]

From Excel Help:

If you are using Microsoft Windows 2000 or later, the Regional Options in Windows Control Panel controls how Excel interprets
two-digit years.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Today I found to my consternation that my broker's updated web pages
| have changed the format of the CSV files I download daily. I have
| to re-work some complex formulas. On my way to accomplishing that,
| I find one change is that what had been four-digit years are now
| two-digit years. My Excel 2002 under XP isn't recognizing them as
| dates. Is there an easy fix here?
|
| Thanks,
| Dallman

 

[Dallman Ross]

From Excel Help:

If you are using Microsoft Windows 2000 or later, the Regional
Options in Windows Control Panel controls how Excel interprets
two-digit years.

Niek, thanks, but I'm not sure that helps in this case. The
settings I have in there are the default for a U.S.-localized
version of Excel 2002. It says to interpret a two-digit year
as between 1030 and 2029, and that's fine.

My problem is, the entry M/dd/yy in the CSV file seems to
Excel to be a non-date string. I have formatted the column
for dates -- that makes no difference.

Dallman

 

[Dallman Ross]

My problem is, the entry M/dd/yy in the CSV file seems to
Excel to be a non-date string. I have formatted the column
for dates -- that makes no difference.

Oh, brother; I see. The jerks inserted a blank space in front now
in all the columns in the CSV file. I'm not sure yet what the best
way to handle this is -- I'm not wanting to massage the data I
download and import.

Dallman

 

[Dave Peterson]

Maybe you can
select that range (column?)
edit|Replace
what: (spacebar)
with: (leave blank)
replace all

or even select that column (one column at a time)
data|text to columns

 

[Dallman Ross]

Maybe you can
select that range (column?)
edit|Replace
what: (spacebar)
with: (leave blank)
replace all

or even select that column (one column at a time)
data|text to columns

Thanks. The whole thing is a pain, because for example the names
of securities in Column A all now start with a leading space.
I re-imported the file and selected space as one of the optional
field delimiters, and checked the tickbox to use multiple
contiguous instances of a field as one; but still, the spaces
are there after import -- because the dodo who formatted the
data now has the header-row names shifted one character to the
left. So we get:

Description Quantity Date ...
Advanced Micro Devices Inc 90 6/8/06 etc.

Actually, re-initiating the data import does allow me to have the
dates be read as dats. So that's very good. But the leading
space before the security names is completely messing up my
lookup tables.

Whoever the bozo is who suddenly decided to alter the internal format
of CSV files downloaded by many thousands of customers with good
frequency ought to have his head examined. (To the good, they
finally fixed a spelling error that had "Purchase Price" written
without the final "e". However, now I just noticed the header fields
are not importing right, because of the offset of one space character.
They get shifted over midway across the table. What a mess!

The IT folks also decided to change the bottom of one of the tables
to have four informational lines (such as totals) at the end
instead of the former two. Again, I have to change my macros to
adjust. But the other file of the pair I use still has two info
lines at the end.

Dallman

----------------

 

[Dallman Ross]

Oh, brother; I see. The jerks inserted a blank space in front
now in all the columns in the CSV file. I'm not sure yet what
the best way to handle this is -- I'm not wanting to massage the
data I download and import.

It's taking a bit of work, but now I've mostly fixed things via
a new data query and the TRIM function. I still think it was
stupid of the broker's people to suddenly insert leading spaces
inside CSV fields. This is right out of the file by way of example:

Description,Quantity,Date Acquired,Purchase Price,...
" ADVANCED MICRO DEVICES INC"," 90.0000"," 6/08/06"," 27.42",...

Dallman

 

[Dave Peterson]

You may want to speak with them and tell them not to fix it. If they notice it,
then they might think that it's a problem that should be corrected and then
you'll be distressed again.

 

[Dallman Ross]

You may want to speak with them and tell them not to fix it. If
they notice it, then they might think that it's a problem that
should be corrected and then you'll be distressed again.

I appreciate what you're saying, Dave -- but I think they
should fix it. I think it's bad practice to insert leading
spaces in CSV files, and will only lead to devlish trouble.
I also think the database guy who approved that should be
reprimanded.

My TRIM won't hurt if they change it back. I only need it
in the security-name column. The other leading blanks
turn out not to be a major problem now. The dates are
working okay too, as 2-digit years, since I reset my
query and explicitly stated those date columns are dates.
So if they change that back too, I'm still okay.

I'm struggling with more problems their changes have introduced
in my macro results, though. I'll go to bed now (it's 3 a.m.
where I am, which is Germany). I'll have to tweak more
tomorrow. I really had something else in mind to do for
this weekend. :-(

Dallman

=======================

Oh, brother; I see. The jerks inserted a blank space in
front now in all the columns in the CSV file. I'm not sure
yet what the best way to handle this is -- I'm not wanting to
massage the data I download and import.

It's taking a bit of work, but now I've mostly fixed things via
a new data query and the TRIM function. I still think it was
stupid of the broker's people to suddenly insert leading spaces
inside CSV fields. This is right out of the file by way of
example [both lines below are in the file at the top]:

Description,Quantity,Date Acquired,Purchase Price,...
" ADVANCED MICRO DEVICES INC"," 90.0000"," 6/08/06"," 27.42",...

Dallman

 

[Dave Peterson]

I agree with you. It's much better practice to fix the original data (or the
dump of the original data).
But I think I would have a conversation that you'd like to be notified before
any changes go online. (Maybe they'll abbreviate a header and change your
columns again???)

You may want to speak with them and tell them not to fix it. If
they notice it, then they might think that it's a problem that
should be corrected and then you'll be distressed again.

I appreciate what you're saying, Dave -- but I think they
should fix it. I think it's bad practice to insert leading
spaces in CSV files, and will only lead to devlish trouble.
I also think the database guy who approved that should be
reprimanded.

My TRIM won't hurt if they change it back. I only need it
in the security-name column. The other leading blanks
turn out not to be a major problem now. The dates are
working okay too, as 2-digit years, since I reset my
query and explicitly stated those date columns are dates.
So if they change that back too, I'm still okay.

I'm struggling with more problems their changes have introduced
in my macro results, though. I'll go to bed now (it's 3 a.m.
where I am, which is Germany). I'll have to tweak more
tomorrow. I really had something else in mind to do for
this weekend. :-(

Dallman

=======================

In <[email protected]>, Dallman Ross
<dman@localhost.> spake thusly:

Oh, brother; I see. The jerks inserted a blank space in
front now in all the columns in the CSV file. I'm not sure
yet what the best way to handle this is -- I'm not wanting to
massage the data I download and import.

It's taking a bit of work, but now I've mostly fixed things via
a new data query and the TRIM function. I still think it was
stupid of the broker's people to suddenly insert leading spaces
inside CSV fields. This is right out of the file by way of
example [both lines below are in the file at the top]:

Description,Quantity,Date Acquired,Purchase Price,...
" ADVANCED MICRO DEVICES INC"," 90.0000"," 6/08/06"," 27.42",...

Dallman

 

[Dallman Ross]

In <[email protected]>, Dave Peterson
<[email protected]> spake thusly:

As for getting them to talk to me, they are most assuredly
not good at that. This is Smith Barney, owned for a few years
now by Citicorp. It would be totally cool if they had their
coders talk to me, e.g., by email, since I am one of the more
active traders at SB and I also know quite a bit more than the
typical user about computers and coding theory, and since I
notice, debug, and complain about many bugs. However, they
keep so many layers of drones between me and anybody important,
I can't do a thing. It's aggravating to call the phone menuing
system (which I pay to do from Germany -- no toll-free) and
sit through the recordings and get connected to a drone who runs
through a help-desk questionnaire each time about what OS I have,
what browser I use, etc. As if the CSV files they provide have
anything to do with my OS or my browser. Etc. Then half the time
a "Level-II Tech" can't be found and they promise to call me back,
which they almost never do. Etc. When I do get a Level-II, he
or she is sometimes halfway intelligent, sometimes not so much;
but in any case almost never as versed in the issues I'm looking
at as I am. It's incredibly frustrating.

If I ran the place, I'd have a beta team of volunteers such as
me who are willing to give feedback and tell the coders what's
wrong with what they're about to do. But they won't let me
anywhere near an actual coder.

They can't even get my address right on postal mail! I spent
three full years complaining at every mailing, because I will not
accept that a customer with a lot of money on deposit at their
institution, who has an active relationship with their full-service
broker, can't have his address written correctly on all business
correspondence. I called or wrote them every single time. I
said I would not give up until I died, and my actuarial expected
lifespan is probably another 35-40 years. Still, it was as bad
as pulling teeth before the last error was fixed, which happened
only last week.

Best,
Dallman

===============================================

I agree with you. It's much better practice to fix the original
data (or the dump of the original data).
But I think I would have a conversation that you'd like to be
notified before any changes go online. (Maybe they'll abbreviate
a header and change your columns again???)

You may want to speak with them and tell them not to fix it.
If they notice it, then they might think that it's a problem
that should be corrected and then you'll be distressed again.

I appreciate what you're saying, Dave -- but I think they
should fix it. I think it's bad practice to insert leading
spaces in CSV files, and will only lead to devilish trouble.
I also think the database guy who approved that should be
reprimanded.

[snip]

 

[Dave Peterson]

I feel your pain. Before my company was swallowed up by a bigger company, we
had a programming department, a development department, and then the users. All
under the same roof. So there would be ad hoc meetings to discuss problems.

The users would go through the development group for enhancement/bugs. The
developers verified that it was a problem with the program before sending it to
the IT group.

And the IT group never put anything into production unless it was tested by the
development group. Yep, errors occurred, but I like to think that the number
was smaller than it could have been.

After the company was swallowed up and we were assimilated, the phone calls go
directly to the programming department--often in India or China.

Life will never be the same.

In <[email protected]>, Dave Peterson
<[email protected]> spake thusly:

As for getting them to talk to me, they are most assuredly
not good at that. This is Smith Barney, owned for a few years
now by Citicorp. It would be totally cool if they had their
coders talk to me, e.g., by email, since I am one of the more
active traders at SB and I also know quite a bit more than the
typical user about computers and coding theory, and since I
notice, debug, and complain about many bugs. However, they
keep so many layers of drones between me and anybody important,
I can't do a thing. It's aggravating to call the phone menuing
system (which I pay to do from Germany -- no toll-free) and
sit through the recordings and get connected to a drone who runs
through a help-desk questionnaire each time about what OS I have,
what browser I use, etc. As if the CSV files they provide have
anything to do with my OS or my browser. Etc. Then half the time
a "Level-II Tech" can't be found and they promise to call me back,
which they almost never do. Etc. When I do get a Level-II, he
or she is sometimes halfway intelligent, sometimes not so much;
but in any case almost never as versed in the issues I'm looking
at as I am. It's incredibly frustrating.

If I ran the place, I'd have a beta team of volunteers such as
me who are willing to give feedback and tell the coders what's
wrong with what they're about to do. But they won't let me
anywhere near an actual coder.

They can't even get my address right on postal mail! I spent
three full years complaining at every mailing, because I will not
accept that a customer with a lot of money on deposit at their
institution, who has an active relationship with their full-service
broker, can't have his address written correctly on all business
correspondence. I called or wrote them every single time. I
said I would not give up until I died, and my actuarial expected
lifespan is probably another 35-40 years. Still, it was as bad
as pulling teeth before the last error was fixed, which happened
only last week.

Best,
Dallman

===============================================

I agree with you. It's much better practice to fix the original
data (or the dump of the original data).
But I think I would have a conversation that you'd like to be
notified before any changes go online. (Maybe they'll abbreviate
a header and change your columns again???)

In <[email protected]>, Dave Peterson
<[email protected]> spake thusly:

You may want to speak with them and tell them not to fix it.
If they notice it, then they might think that it's a problem
that should be corrected and then you'll be distressed again.

I appreciate what you're saying, Dave -- but I think they
should fix it. I think it's bad practice to insert leading
spaces in CSV files, and will only lead to devilish trouble.
I also think the database guy who approved that should be
reprimanded.

[snip]

 

[Dallman Ross]

I feel your pain. Before my company was swallowed up by a
bigger company, we had a programming department, a development
department, and then the users. All under the same roof. So
there would be ad hoc meetings to discuss problems.

I see you know just what I mean.

Life will never be the same.
Yup.

Cheers,
Dallman

[rest snipped]