Searching \ for '[EE] Using Excel for dashboard applications' in subject line. ()
Make payments with PayPal - it's fast, free and secure! Help us get a faster server
FAQ page: www.piclist.com/techref/index.htm?key=using+excel+dashboard
Search entire site for: 'Using Excel for dashboard applications'.

Exact match. Not showing close matches.
PICList Thread
'[EE] Using Excel for dashboard applications'
2007\09\07@205757 by Vitaliy

flavicon
face
part 1 4316 bytes content-type:text/plain; format=flowed; charset="iso-8859-1"; (decoded 7bit)

Tony Smith wrote a post over a week ago, which even I missed (as it was
buried in another thread). Although this stuff may be obvious to some, I
believe it deserves its own thread -- it's definitely been an eye opener to
me.

When Tony said:

> Excel + 'Get External Data' + Autorefresh + PivotTable/PivotChart keeps a
> lot of people happy, and it's dead easy to pull stuff off the web or a
> database, summarise it in a pretty graph, and have it refresh every few
> minutes (aka dashboard apps).  The last guy hadn't finished rambling about
> he'd use XML, AJAX and Java and many other letters of the alphabet when I
> showed him the finished result.  He hasn't spoken to me since  :)

...frankly, I thought that he's exaggerating (who doesn't, to make a good
story better?). However, it literally took me about five minutes to go
through Tony's tutorial, and display the finished result.

I never suspected that importing an HTML table into Excel is simply a matter
of point-and-click!

{Quote hidden}

I wonder if any "real" development suites allow for such simple
point-and-click import of data from the internet?

Vitaliy


part 2 12628 bytes content-type:application/x-zip-compressed; (decode)

part 3 35 bytes content-type:text/plain; charset="us-ascii"
(decoded 7bit)

2007\09\07@211516 by Jake Anderson

flavicon
face
Just to let you know i just opened this in open office in linux and it
all "just worked", no tweaking or nothin, just filled it in by its own self.
So you could use this method for clients and not have to bill them for a
copy of office (if they don't have one).

Vitaliy wrote:
{Quote hidden}

2007\09\07@212355 by Vitaliy

flavicon
face
Jake Anderson wrote:
> Just to let you know i just opened this in open office in linux and it
> all "just worked", no tweaking or nothin, just filled it in by its own
> self.
> So you could use this method for clients and not have to bill them for a
> copy of office (if they don't have one).

Great to hear, thank you!

This brings up another question I had: what *can't* you do in OpenOffice,
that you can do in MS Office, in a common office environment? Are the files
completely interchangeable?

Vitaliy

2007\09\07@231106 by Gerhard Fiedler

picon face
Vitaliy wrote:

> This brings up another question I had: what *can't* you do in OpenOffice,
> that you can do in MS Office, in a common office environment? Are the
> files completely interchangeable?

I vaguely remembered that there were some things in Excel that I did miss
in Calc, but no details... There seems to be quite a bit on the web, though
(mostly biased, it seems, but still :)

<http://www.google.com/search?q=comparison+openoffice+microsoft+office>

<www.openoffice.org/product/docs/ms2007vsooo2.pdf>
<http://www.linux.com/articles/45689>
<http://www.techsoup.org/learningcenter/software/page4765.cfm>

Gerhard

2007\09\08@011229 by Sergey Dryga

face picon face
Gerhard Fiedler <lists <at> connectionbrazil.com> writes:

>
> Vitaliy wrote:
>
> > This brings up another question I had: what *can't* you do in OpenOffice,
> > that you can do in MS Office, in a common office environment? Are the
> > files completely interchangeable?
>
> I vaguely remembered that there were some things in Excel that I did miss
> in Calc, but no details... There seems to be quite a bit on the web, though
> (mostly biased, it seems, but still :)
>
> <www.google.com/search?q=comparison+openoffice+microsoft+office>
>
> <www.openoffice.org/product/docs/ms2007vsooo2.pdf>
> <http://www.linux.com/articles/45689>
> <www.techsoup.org/learningcenter/software/page4765.cfm>
>
> Gerhard
>
Some functions present in XL were not in OO (as of 1-2 years ago), or the names
are different.  I tried to use my XL files in OO and failed miserably, mostly
because I use a lot of functions (usually :-)).  I do not know if this has been
resolved since I checked.

This is not to say that OO cannot do things excel can, it's a portability issue,
not unctionality.

Sergey



2007\09\08@020536 by Mark Rages

face picon face
On 9/7/07, Sergey Dryga <spam_OUTsdrygaTakeThisOuTspamnc.rr.com> wrote:
{Quote hidden}

Can OO do linear regression?   I had a need for that this week.

Regards,
Mark
markrages@gmail
--
Mark Rages, Engineer
Midwest Telecine LLC
.....markragesKILLspamspam@spam@midwesttelecine.com

2007\09\08@024207 by Sergey Dryga

face picon face
Mark Rages <markrages <at> gmail.com> writes:

><SNIP>
>
> Can OO do linear regression?   I had a need for that this week.
>
> Regards,
> Mark
> markrages <at> gmail

It can if you use functions: SLOPE(), INTERCEPT(), RSQ().  I did not see an
option to do this in a graph like you can do in XL.

Sergey



2007\09\08@124029 by Paul Hutchinson

picon face
> -----Original Message-----
> From: piclist-bouncesspamKILLspammit.edu On Behalf Of Sergey Dryga
> Sent: Saturday, September 08, 2007 2:42 AM
>
> Mark Rages <markrages <at> gmail.com> writes:
>
> ><SNIP>
> >
> > Can OO do linear regression?   I had a need for that this week.
> >
> > Regards,
> > Mark
> > markrages <at> gmail
>
> It can if you use functions: SLOPE(), INTERCEPT(), RSQ().  I did
> not see an option to do this in a graph like you can do in XL.
>
> Sergey

OOCalc can add a regression line to a graph. See:
<www.openofficetips.com/2005/04/13/regression-analysis-ii-basic-funct
ions-charting/>

Also see the LINEST and LOGEST functions for more regression capabilities.

For a spreadsheet program that has more math functions than Excel or OOCalc
check out Gnumeric. <http://www.gnome.org/projects/gnumeric/>

Paul


2007\09\10@110604 by Tony Smith

picon face
> I never suspected that importing an HTML table into Excel is
> simply a matter of point-and-click!
>
>
> I wonder if any "real" development suites allow for such
> simple point-and-click import of data from the internet?
>
> Vitaliy


LOL  :)

Tony

2007\09\10@111108 by Tony Smith

picon face
> I vaguely remembered that there were some things in Excel
> that I did miss in Calc, but no details... There seems to be
> quite a bit on the web, though (mostly biased, it seems, but still :)
>
> <www.google.com/search?q=comparison+openoffice+microsof
t+office>
>
> <www.openoffice.org/product/docs/ms2007vsooo2.pdf>
> <http://www.linux.com/articles/45689>
> <http://www.techsoup.org/learningcenter/software/page4765.cfm>


Most of it is VBA and ActiveX, not a great loss in most cases.  The rest of
it is how stuff *should* work, a bit like the 1900 leap year bug XL has
(which it inherited from Lotus 123, so it's actually a feature), or
statistics nerds arguing about the 23rd decimal place.

That last article is actually on Word, and has a few biases & errors.  Right
at the beginning is this quote:

"Writer consistently allows more control, offering settings for hyphenation,
automatic page breaks, and the last line in a fully justified paragraph."

...which is bollocks.  I'm not sure what a "fully justified paragraph"
actually is, but if he's talking widow/orphan control, he's wrong.

Widow/orphan control is where you have paragraph that j-u-s-t stretches over
a page.  A line break is inserted, thus:

       If you go type type tpye type
       just a little bit too much,
       you might find the last word
       or so ends up on the next
       -----------------------------
       page.

That's an 'orphan', widow/orphan control pushes another line onto the next
page so it won't get lonely, like so:

       If you go type type type type
       just a little bit too much,
       you might find the last word
       -----------------------------
       or so ends up on the next
       page.

A 'widow' is the reverse.  Word enables this by default, you've probably
never noticed it working.  Page breaks & hypens are in the same spot in the
Styles dialog, not sure how he missed those.  There's also the 'keep lines
together' option, which overrides widow/orphan by never allowing a page
break in a paragraph.  That'll drag the entire paragraph to the next page in
this case.  Not use much for plain text, it makes things look strange.  Good
for small lists though.

His complaints about templates are wrong, the 'auto update' options are off
by default, and the 'add to template' option resets itself to off after use
anyway (that stops users screwing your templates, which you should have
locked anyway).  If you cut-n-paste from one doc to another, then the
formatting of what you've pasted will change (if styles are used) to match
the target document, which is what you want!  Converting 'custom' styles is
pretty painless in Word 2003.

He missed table styles (Word XP) while complaining about autoformat (both
Word & OO), and doesn't mention fields or mail merge.  Fields were a "Word
sucks" - "No it doesn't, RTFM" thread a while back.  The cascading styles
outline in Writer sounds neat.

Outlining rocks, although Word 2003 removed the nifty 1|2|3|4 etc boxes,
replacing them with a lame drop-down list.  Word's document map is handy
too.

Master documents and lists generally suck in Word though.

<snarky> He can't be much of a journalist if he didn't bitch about the word
count not working properly.  Ha. </snarky>

All up, he might as well go and piss into the wind, since the typical user
(99%) doesn't use ANY of this stuff.  That's education, not the products.
Sigh.

Tony

2007\09\10@124806 by Rolf

face picon face
Tony Smith wrote:
{Quote hidden}

[snip]

> Tony
>
>  


Full justification is combined left-and-right justification. Look at the
columns of the newspaper, you will see full justification, where the
line is kept against the left margin, but then stretched to the right so
that all the lines in a column have the same width, regardless of the
number of characters in the line. This can become amusing when the
columns are narrow, and the words are long... you see very stretched
words like: "a    n   d      i   f".

Rolf

2007\09\10@133201 by Tony Smith

picon face
{Quote hidden}

Yeah, I kinda figured that what he meant, but you never know.  Word Perfect
used to do it that way, and everyone said it sucked.  MS Word has a bunch of
options to make it justify like WP does, so I don't see exactly what the
problem was.

By default, Word doesn't justify the last line if it's short (looks funny
either way), but does if it's longer, like if it's half the page width.
Maybe Writer lets you justify short lines (or not), rather than Word
deciding for you.  Mind you, Word probably will let you do that, if you feel
like fiddling with a couple of dozen setting.

Tony

2007\09\10@154229 by Gerhard Fiedler

picon face
Tony Smith wrote:

>> <www.techsoup.org/learningcenter/software/page4765.cfm>

> That last article is actually on Word, and has a few biases & errors.
> Right at the beginning is this quote:
>
> "Writer consistently allows more control, offering settings for
> hyphenation, automatic page breaks, and the last line in a fully
> justified paragraph."
>
> ...which is bollocks. I'm not sure what a "fully justified paragraph"
> actually is [...]

First shoot, then ask, hm? :)

> [...] but if he's talking widow/orphan control, he's wrong.

Apparently he's not talking about widow/orphan control, because that has
nothing to do with left/right/fully justified or centered (which are the
four common justification formats).

Writer allows to define how the last line of a fully justified paragraph
should be aligned. In Word, it's left justified, but there is not much that
justifies this assumption: a fully justified paragraph is at the same time
left justified, right justified and centered. The last line may have to be
any of the three -- and Writer conveniently provides these three choices.

Gerhard

2007\09\10@190137 by Tony Smith

picon face
{Quote hidden}

Yep, see other comment.  Should have Googled it first like everyone else, I
suppose.  I got distracted by the missing-but-actually-there options.

Anyway, if you think about it, it's a useless option.  Right align is
pointless (yuk), and forcing either left or justify (by a style) is
pointless too.  To be useful, you'd have to set it for each individual
paragraph, and life's too short for that.

He's still wrong on hypenation, styles, templates, tables etc.

Maybe Writer can add click-and-type (if it doesn't already) to fill out the
'useful features' list.

Tony

2007\09\11@001635 by Gerhard Fiedler

picon face
Tony Smith wrote:

>> <www.techsoup.org/learningcenter/software/page4765.cfm>

> That last article is actually on Word, and has a few biases & errors.
> Right at the beginning is this quote:
>
> "Writer consistently allows more control, offering settings for
> hyphenation, automatic page breaks, and the last line in a fully
> justified paragraph."
>
> ...which is bollocks. I'm not sure what a "fully justified paragraph"
> actually is [...]

First shoot, then ask, hm? :)

> [...] but if he's talking widow/orphan control, he's wrong.

Apparently he's not talking about widow/orphan control, because that has
nothing to do with left/right/fully justified or centered (which are the
four common justification formats).

Writer allows to define how the last line of a fully justified paragraph
should be aligned. In Word, it's left justified, but there is not much that
justifies this assumption: a fully justified paragraph is at the same time
left justified, right justified and centered. The last line may have to be
any of the three -- and Writer conveniently provides these three choices.

Gerhard

2007\09\11@002223 by Gerhard Fiedler

picon face
Tony Smith wrote:

{Quote hidden}

I don't follow you here. (Maybe you think about it? :)

A paragraph can basically be left aligned (all lines have the same left end
and the right end varies), right aligned (all lines have the same right end
and the left end varies) or centered (the distances of left and right ends
to a straight vertical line left and right are the same).

Now when you justify a paragraph -- that is, you make left and right ends a
straight vertical line --, you basically still maintain the quality of
left, right or center, it just is visible only in the last line. It's just
that Word doesn't do this -- all justified paragraphs in Word are
underneath left-aligned --, and so all who grew up with Word don't know it
otherwise.

> Right align is pointless (yuk), and forcing either left or justify (by a
> style) is pointless too.  To be useful, you'd have to set it for each
> individual paragraph, and life's too short for that.

You must be talking about something else. Giving a justified paragraph a
right-aligned or centered last partial line is just as valid as giving it a
left-aligned last partial line -- it's just less "Wordy"...

Gerhard

2007\09\11@062824 by Alan B. Pearce

face picon face
>Right align is pointless (yuk),

I can see you haven't needed to enter numbers into a table.

2007\09\11@094509 by Tony Smith

picon face
{Quote hidden}

Yeah, now I know what you're talking about, and Word doesn't do it.  Real
typesetting programs do that stuff.  I've had to do it exactly once in Word,
and for the life of me I can't remember how.  It's been years since I had to
worry about that stuff.

When you say 'paragraph justify centered', I take it you mean 'align to
spline' or whatever it's usually called.  For some reason I was envisioning
this:

       Blah blah blah blah
       blah blah blah blah
               blah

LOL.  I need more sleep.  You probably mean this:

       Blah blah blah blah  |  Blah blah blah blah
       blah blah blah blah  |  blah blah blah blah
                      blah  |  blah

Odd pages are 'justify & last right', even pages 'justify & last left'.  Not
something you do much in a lifetime.  Word does 'fully justified' in a
half-arsed way.  You insert a hard return at the end of the line, so you get
this:

       Blah blah blah blah
       blah blah blah blah
       B    l     a      h

Nice.  The half-arsed bit is now you get a bonus blank line (due to 2
returns), but not in Writer.

I don't justify text anyway, to do it right you need to enable hyphenation,
and to do that right you need to set each instance individually, then fiddle
with the kerning & spacing.  Done wrong it's actually hard to read.  The
typesetter can do that, that's their job.

If Writer really want to grab some market share they should look at handling
graphics (anchors & such) better than Word does.  The article didn't mention
that either, so I guess Writer sucks too.

Well, Word handles it just fine, it's just such a pain-in-the-arse to fiddle
with it.  For every picture you get to go thru the whole 'set 40 options
here & there' bit.  It really needs a 'picture styles' setting.

Captions suck too.  Putting them in, setting the label and creating x-refs
works well enough (custom labels would be nice), but Word treats the graphic
& the caption as two separate items, not buddies like it should, so if
you're not careful you wind up with the picture on one page, and the caption
three or four pages down.  Gah.

What I do is create a new table style.  I apply that to a picture, and it
embeds it into a 1 cell table.  The caption goes into the cell as well, so
now they are buddies like they should be.  Since it's a style, I can set the
spacing around pictures fairly easily by adjusting the cell spacing.  All of
them at once, not once for every frigging picture.  It really only works for
'top & bottom' wrapping, but for tech docs (screenshots) it works well.  (Of
course, instead of 50 pictures, I now have 50 pictures and 50 tables.)
Other people tend not to break this as much.  For 'this picture goes in the
middle of the page and doesn't ever move' stuff, not so well.

Tony

2007\09\11@095515 by Tony Smith

picon face
> >Right align is pointless (yuk),
>
> I can see you haven't needed to enter numbers into a table.
> --


Different type of right align, I managed to confuse myself.  Gerhard was
talking about justifying the entire paragraph, but right aligning the very
last line.  Something like this:

       If you go type type type type
       just a little  bit too  much,
       you might  find the last word
       or  so ends  up on  the  next
                       page  or  so.

Word does this:

       or  so ends  up on  the  next
       page or so.

or maybe this:

       or  so ends  up on  the  next
       page  or  so.

...depending on the length of the last line.  But not the first one.

Not something you'd normally use, arty or advertising stuff excepted.
Anyway, you should be using decimal tabs to line up numbers properly.
Unless they don't have decimals points, then carry on  :)  

Still works though.  If the number of digits after the decimal varies, then
it can look a bit weird.

Tony

2007\09\11@100336 by Gerhard Fiedler

picon face
Tony Smith wrote:

> When you say 'paragraph justify centered', I take it you mean 'align to
> spline' or whatever it's usually called.  For some reason I was envisioning
> this:
>
>        Blah blah blah blah
>        blah blah blah blah
>                blah
>
> LOL.  I need more sleep.  You probably mean this:
>
>        Blah blah blah blah  |  Blah blah blah blah
>        blah blah blah blah  |  blah blah blah blah
>                       blah  |  blah

Nope, the first. But let's not go there... if the few words so far didn't
suffice, more it's not worth :)

> I don't justify text anyway,

I don't either. Even when well done I don't find it good to read; it's more
a "looks good from far enough away that you can't read anyway" feature.

> Captions suck too.  

Oh, yes, I'm all with you on this.

> What I do is create a new table style.  

I've had reasonable success with placing pictures and their captions into a
frame.

Gerhard

2007\09\11@104153 by Tony Smith

picon face
{Quote hidden}

Ok, not sure why you'd want option 1, but unless you've got a link, well
anyway.  And that was spine, not spline, as in the spine of the book (the |
line thingy).

I played with using frames, but found other people broke the 'floating text
box thingy'.  Tables they understand, and if they put a new picture in, I
can just click the picture (and highlight the caption too if it's there),
click the table style (called Picture, go figure) and it's fixed.

Picture styles, c'mon people, get to it.  And billg, if you're listening,
when I update a document from a revised template, how about copying tables
sytles too?  And the DATABASE field command still doesn't work properly.

Tony

...and that's probably enough typography nerd stuff for a while.

2007\09\12@120922 by Tony Smith

picon face
> >> <http://www.linux.com/articles/45689>>
>  
> > That last article is actually on Word, and has a few biases
> & errors.


Bruce is at it again, posting an article on Word 2007 vs OO 2.2. -
<http://www.linux.com/feature/118986>.  Choice quote in the comments: "It's
not denial. I'm just selective about the reality I accept."

Amusingly, it's getting fairly well flamed over at Slashdot of all places as
well, see <http://linux.slashdot.org/linux/07/09/11/2049204.shtml>.  If even
the Linux fanboys think it's biased... there can't be that many M$ shills
over there.

His major complaint is he doesn't like the ribbon (OO doesn't have it, so
it's better), but it's interesting to compare the two articles.  

He doesn't complain about templates this time (he was wrong anyway),
apparently Word can't handle documents over 40 pages now (my page count must
be broken), and I'm not sure what his obsession with headers & footers is.
You see them in layout view, but who wants to anyway?  It's a footer, it's
not as if it changes much.

And still no mention of find & replace, graphics, captions, macros, table
styles, fields, mail merge, word count, let alone a decent comparison of
equation editors, spelling & grammar, etc etc.

Tony

More... (looser matching)
- Last day of these posts
- In 2007 , 2008 only
- Today
- New search...