Vertical Rule (TODAY marker) fails in XL2007

[Guest]

In XL2003 annual multiple-account financial charts with daily data, I used a
second data set X1=X2=today() & Y1=[a high number], Y2=[a low number] to
create a vertical "today" rule on the page for clarity. Won't display in
XL2007 notwithstanding charting hotfix applied. Any comments, workarounds?
I can't even see how to create it from scratch in XL2007, the thing seems to
preclude multiple X-value data sets.

 

[Jon Peltier]

Are the X values in the main chart dates? Are they plotted as dates
(weekends have a place on the axis although no trades occur then) or as
categories? If they are plotted as categories, the X1=X2=TODAY() will be way
off scale.

- Jon

 

[Guest]

All X-values are dates. Main chart data is column1=dates, as required for
transactions (may be multiple or none); remaining columns are various
accounts & subtotals (bank, stocks, margin, etc). Separate data set is
entered on page one: two rows of data only, C1'=date (both=today()), C2'=hi
valu in R1, lo (negative) value in R2. The idea is to generate a perpetually
advancing "today" rule across the chart. Dates appear in chart as
abbreviations for month JFMAMJJASOND though display in data page as metric
date yy.mm.dd. Multiple subtotals display on chart as scatter chart (lined)
of daily data. The whole thing works like a charm in XL2003, and even works
in XL2007 (after essential hotfix) except for the vertical "today" rule.
(Why such a Rule at all? Because I plot pro-forma advance data to year end.)

Driving me nuts, but there must be a way. I'm missing the clunky
nuts-&-bolts approach of prior XL versions - perhaps not Mac-like in
aesthetics, but incredibly powerful.

Are the X values in the main chart dates? Are they plotted as dates
(weekends have a place on the axis although no trades occur then) or as
categories? If they are plotted as categories, the X1=X2=TODAY() will be way
off scale.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

In XL2003 annual multiple-account financial charts with daily data, I used
a
second data set X1=X2=today() & Y1=[a high number], Y2=[a low number] to
create a vertical "today" rule on the page for clarity. Won't display in
XL2007 notwithstanding charting hotfix applied. Any comments,
workarounds?
I can't even see how to create it from scratch in XL2007, the thing seems
to
preclude multiple X-value data sets.

 

[Jon Peltier]

The X axis is a date-scale axis? The axis min and max scale parameters
encompass the '=TODAY()' entries?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

All X-values are dates. Main chart data is column1=dates, as required for
transactions (may be multiple or none); remaining columns are various
accounts & subtotals (bank, stocks, margin, etc). Separate data set is
entered on page one: two rows of data only, C1'=date (both=today()),
C2'=hi
valu in R1, lo (negative) value in R2. The idea is to generate a
perpetually
advancing "today" rule across the chart. Dates appear in chart as
abbreviations for month JFMAMJJASOND though display in data page as metric
date yy.mm.dd. Multiple subtotals display on chart as scatter chart
(lined)
of daily data. The whole thing works like a charm in XL2003, and even
works
in XL2007 (after essential hotfix) except for the vertical "today" rule.
(Why such a Rule at all? Because I plot pro-forma advance data to year
end.)

Driving me nuts, but there must be a way. I'm missing the clunky
nuts-&-bolts approach of prior XL versions - perhaps not Mac-like in
aesthetics, but incredibly powerful.

Are the X values in the main chart dates? Are they plotted as dates
(weekends have a place on the axis although no trades occur then) or as
categories? If they are plotted as categories, the X1=X2=TODAY() will be
way
off scale.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

In XL2003 annual multiple-account financial charts with daily data, I
used
a
second data set X1=X2=today() & Y1=[a high number], Y2=[a low number]
to
create a vertical "today" rule on the page for clarity. Won't display
in
XL2007 notwithstanding charting hotfix applied. Any comments,
workarounds?
I can't even see how to create it from scratch in XL2007, the thing
seems
to
preclude multiple X-value data sets.

 

[Guest]

Exactly. X-range is current calendar year, with version xx.12.31 archived at
year end. Pulling up a prior year's version for review naturally shows no
"today rule", as the rule is off-scale. Current year version's rule is
always in range.

One thing I haven't tried: Since XL2007 seems to dislike a second x-range,
I could replace the date data with a column appended to the main range, whose
x-values are the same as the financial data & whose y-values are
if(date=today(),high-number,low-number). I didn't go that way originally
because the two-point ruler is elegant/efficient, and because there is NOT a
1:1 correspondence between calendar & "x" - transactions govern number of
occasions, if any, of a given date. So the "if" solution will give me a
disappearing rule on transaction-less days.

Current workaround: add three x-values in main column all =today(),
highlighted in distinctive colour, assigning dummy hi/lo/=[R-2] values to an
account of lesser significance. Works, but I don't like dummy data in
financials, and it requires a daily sort-all-on-date (arggh, XL2007 sort
doesn't seem to auto-default to usual sort the way XL2003 did, need to stick
a macro button in there if this is the answer). /tmm

The X axis is a date-scale axis? The axis min and max scale parameters
encompass the '=TODAY()' entries?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

All X-values are dates. Main chart data is column1=dates, as required for
transactions (may be multiple or none); remaining columns are various
accounts & subtotals (bank, stocks, margin, etc). Separate data set is
entered on page one: two rows of data only, C1'=date (both=today()),
C2'=hi
valu in R1, lo (negative) value in R2. The idea is to generate a
perpetually
advancing "today" rule across the chart. Dates appear in chart as
abbreviations for month JFMAMJJASOND though display in data page as metric
date yy.mm.dd. Multiple subtotals display on chart as scatter chart
(lined)
of daily data. The whole thing works like a charm in XL2003, and even
works
in XL2007 (after essential hotfix) except for the vertical "today" rule.
(Why such a Rule at all? Because I plot pro-forma advance data to year
end.)

Driving me nuts, but there must be a way. I'm missing the clunky
nuts-&-bolts approach of prior XL versions - perhaps not Mac-like in
aesthetics, but incredibly powerful.

Are the X values in the main chart dates? Are they plotted as dates
(weekends have a place on the axis although no trades occur then) or as
categories? If they are plotted as categories, the X1=X2=TODAY() will be
way
off scale.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


In XL2003 annual multiple-account financial charts with daily data, I
used
a
second data set X1=X2=today() & Y1=[a high number], Y2=[a low number]
to
create a vertical "today" rule on the page for clarity. Won't display
in
XL2007 notwithstanding charting hotfix applied. Any comments,
workarounds?
I can't even see how to create it from scratch in XL2007, the thing
seems
to
preclude multiple X-value data sets.

 

[Jon Peltier]

The X axis is a date-scale axis?

I'm not sure we've gotten your answer to this.

Since XL2007 seems to dislike a second x-range

Not necessarily...

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Exactly. X-range is current calendar year, with version xx.12.31 archived
at
year end. Pulling up a prior year's version for review naturally shows no
"today rule", as the rule is off-scale. Current year version's rule is
always in range.

One thing I haven't tried: Since XL2007 seems to dislike a second
x-range,
I could replace the date data with a column appended to the main range,
whose
x-values are the same as the financial data & whose y-values are
if(date=today(),high-number,low-number). I didn't go that way originally
because the two-point ruler is elegant/efficient, and because there is NOT
a
1:1 correspondence between calendar & "x" - transactions govern number of
occasions, if any, of a given date. So the "if" solution will give me a
disappearing rule on transaction-less days.

Current workaround: add three x-values in main column all =today(),
highlighted in distinctive colour, assigning dummy hi/lo/=[R-2] values to
an
account of lesser significance. Works, but I don't like dummy data in
financials, and it requires a daily sort-all-on-date (arggh, XL2007 sort
doesn't seem to auto-default to usual sort the way XL2003 did, need to
stick
a macro button in there if this is the answer). /tmm

The X axis is a date-scale axis? The axis min and max scale parameters
encompass the '=TODAY()' entries?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

All X-values are dates. Main chart data is column1=dates, as required
for
transactions (may be multiple or none); remaining columns are various
accounts & subtotals (bank, stocks, margin, etc). Separate data set is
entered on page one: two rows of data only, C1'=date (both=today()),
C2'=hi
valu in R1, lo (negative) value in R2. The idea is to generate a
perpetually
advancing "today" rule across the chart. Dates appear in chart as
abbreviations for month JFMAMJJASOND though display in data page as
metric
date yy.mm.dd. Multiple subtotals display on chart as scatter chart
(lined)
of daily data. The whole thing works like a charm in XL2003, and even
works
in XL2007 (after essential hotfix) except for the vertical "today"
rule.
(Why such a Rule at all? Because I plot pro-forma advance data to year
end.)

Driving me nuts, but there must be a way. I'm missing the clunky
nuts-&-bolts approach of prior XL versions - perhaps not Mac-like in
aesthetics, but incredibly powerful.

:

Are the X values in the main chart dates? Are they plotted as dates
(weekends have a place on the axis although no trades occur then) or
as
categories? If they are plotted as categories, the X1=X2=TODAY() will
be
way
off scale.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


In XL2003 annual multiple-account financial charts with daily data,
I
used
a
second data set X1=X2=today() & Y1=[a high number], Y2=[a low
number]
to
create a vertical "today" rule on the page for clarity. Won't
display
in
XL2007 notwithstanding charting hotfix applied. Any comments,
workarounds?
I can't even see how to create it from scratch in XL2007, the thing
seems
to
preclude multiple X-value data sets.

 

[Guest]

To clarify: yes, the X-axis is a date scale (units fixed to MONTHS labelled J
thru D). Secondary X-axis ...

....oy. Just figured it out. Ah, the elusive Aha! experience! YES! The
vertical rule appears anew! You pointed me there, bingo. Thanks Jon!

Here's the deal: primary X-axis is date based with min=J and max=D and
unit=1month. But at least insofar as XL2007 is concerned, secondary X-axis
is, though date-based, calibrated to fixed numerical date equivalent to
05.01.01-05.12.31, 2 years out of date. I suppose it was somehow residually
embedded but XL2003 fuzzily dialed it out, letting the primary axis carry the
day. I just looked at it with XL2003 on an adjacent PC running XP/XL2003,
seems both data sets are plotted on a single axis. So... i guess XL2007
introduced the concept of a secondary axis, or at least so interpreted the
data, and magically decided to invoke an old max/min scale?

Thanks a million,

Schooner
Richmond BC Canada

I'm not sure we've gotten your answer to this.

Since XL2007 seems to dislike a second x-range

Not necessarily...

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Exactly. X-range is current calendar year, with version xx.12.31 archived
at
year end. Pulling up a prior year's version for review naturally shows no
"today rule", as the rule is off-scale. Current year version's rule is
always in range.

One thing I haven't tried: Since XL2007 seems to dislike a second
x-range,
I could replace the date data with a column appended to the main range,
whose
x-values are the same as the financial data & whose y-values are
if(date=today(),high-number,low-number). I didn't go that way originally
because the two-point ruler is elegant/efficient, and because there is NOT
a
1:1 correspondence between calendar & "x" - transactions govern number of
occasions, if any, of a given date. So the "if" solution will give me a
disappearing rule on transaction-less days.

Current workaround: add three x-values in main column all =today(),
highlighted in distinctive colour, assigning dummy hi/lo/=[R-2] values to
an
account of lesser significance. Works, but I don't like dummy data in
financials, and it requires a daily sort-all-on-date (arggh, XL2007 sort
doesn't seem to auto-default to usual sort the way XL2003 did, need to
stick
a macro button in there if this is the answer). /tmm

The X axis is a date-scale axis? The axis min and max scale parameters
encompass the '=TODAY()' entries?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


All X-values are dates. Main chart data is column1=dates, as required
for
transactions (may be multiple or none); remaining columns are various
accounts & subtotals (bank, stocks, margin, etc). Separate data set is
entered on page one: two rows of data only, C1'=date (both=today()),
C2'=hi
valu in R1, lo (negative) value in R2. The idea is to generate a
perpetually
advancing "today" rule across the chart. Dates appear in chart as
abbreviations for month JFMAMJJASOND though display in data page as
metric
date yy.mm.dd. Multiple subtotals display on chart as scatter chart
(lined)
of daily data. The whole thing works like a charm in XL2003, and even
works
in XL2007 (after essential hotfix) except for the vertical "today"
rule.
(Why such a Rule at all? Because I plot pro-forma advance data to year
end.)

Driving me nuts, but there must be a way. I'm missing the clunky
nuts-&-bolts approach of prior XL versions - perhaps not Mac-like in
aesthetics, but incredibly powerful.

:

Are the X values in the main chart dates? Are they plotted as dates
(weekends have a place on the axis although no trades occur then) or
as
categories? If they are plotted as categories, the X1=X2=TODAY() will
be
way
off scale.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


In XL2003 annual multiple-account financial charts with daily data,
I
used
a
second data set X1=X2=today() & Y1=[a high number], Y2=[a low
number]
to
create a vertical "today" rule on the page for clarity. Won't
display
in
XL2007 notwithstanding charting hotfix applied. Any comments,
workarounds?
I can't even see how to create it from scratch in XL2007, the thing
seems
to
preclude multiple X-value data sets.