In ,
dk_ spake thusly:

Dallman,

See if I understand your setup...

Your hidden sheet is always updated when the workbook is
opened, and Excel doesn't 'ask' you 'yes or no' for the update
permission. But then it sounds like you have written a macro
that will copy the data from your hidden sheet when you 'run'
the macro?

Not quite, though close. I'll explain further:

1. I download the new data from my broker manually, usually at
least daily. (I am a very active trader. It's what I do for
a living.) I download it as a CSV (comma-separated values)
data-file.

2. I have a sheet set up in Excel that points to the CSV
file as a data source. I set this up initially using the menu
selection: Data - Import External Data - Import Data. Ever
after from the time I set that up, I can simply go to Data -
Refresh Data now on the main menu to pull in the data from the
current file I've just downloaded.

3. I want to do lots more than just display the data the broker
can show me. (Otherwise, I wouldn't need Excel to see it.
I could just look on the broker's web page at my transaction
details.) :-) The rest of my setup, I created to accommodate
my needs in that regard. A main consideration was data
integrity: I found, over the years of using a more direct
approach, that I could too easily introduce errors into my
spreadsheet and not know about it right away. I got very
fatigued with noticing at the oddest moments that something
seemed wrong with my data, then having to spend the next
three-quarter-hour finding the error (often to the tune of a
penny or two!). So that was the impetus for how I've designed
things now.

a) I don't want to disturb the broker's data; that's what
led to the introduction of errors in the past. So I set
up another sheet -- the main one -- into which I simply
reference the columnar data from the imported data sheet.
This is the sheet I can manipulate without fear of trashing
my data. Since I don't want to stare at the tabbed sheet
holding the imported broker's data, I hide it most of
the time. I also protect that sheet.

b) Note that I don't need every column the broker provides.
Suppose, of the 12 columns of supplied data, I'm interested
in working with 9 (and in a different displayed order from
that provided, as well); and I have another 21 columns of
my own devise that comprise calculated fields with more
sophisticated analyses of my trades. I also have some
hidden helper-columns on my main sheet.

c) All the messy steps for updating are done with a macro,
following the manual download of the CSV file. The hidden
sheet is unhidden and gets unprotected; the data query is
refreshed; named ranges are recalculated; the data sheet
is protected once more and again hidden. Now the macro
goes to the main sheet. It unhides all hidden columns
and unfilters all filtered data. It sorts based on a
normally-hidden key column that is simply a reference to
the row numbers on the data sheet. It adds or deletes rows
as needed and re-drags the formulas from the top row to
the new bottom. I have too much data for fast operation
if all the formulas are left in place, so the macro then
changes all cells from formulas to values, except for the
first row. Then it sorts again to my nominal display
preference. (Now the "first," formula, row -- the only
one left that really contains my formulas -- is somewhere
in the middle. I can get back from values to formulas
by re-sorting based on the key, which brings the first,
formula, row back to the top; then dragging down to fill
the formulas to the bottom.) Then the macro re-filters
data to my nominal preference and hides the helper columns
that are normally hidden.

i. There are actually two main sheets and two data sheets:
realized gains and unrealized (current portfolio).
The macro cycles through both doing similar tasks.
There are also chart sheets that update automatically
based on the most recent data.

I'm still cleaning up the last part of the macro. I've had help
here with it in the last month, most especially from Dave Peterson,
who was willing to baby me through the learning process. (Thanks,
Dave!)

Any more questions?

-dman-

================================================== ==================
In article ,
Dallman Ross <dman@localhost. wrote:

In ,
dk_ spake thusly:

Running Excel97, and Excel98 Mac.

I'd like to manually be able to control updating of 'linked
references' in a few workbooks, without having to click the
'No' button in the dialog box that displays when I open each
workbook containing links.

If I uncheck the choice on the 'ToolsOptionsEdit tab',
(Ask to update automatic links), then I don't get the 'ask"
dialog box upon file opening, but then the links update
automatically.

I have three columns of stock quote data that I download,
and I'd like to update each workbook manually by choice.

What's the best way to do this?

Maybe 'linked references' is not best way to do this?

Well, I have similar tasks, but use a different approach. I
do have the Ask dialog turned of in Options. But I also use a
(normally hidden) worksheet in my workbook to store and permit
refreshes of the data source, which is a CSV file I download
often from my broker. I have the main sheet set up as series
either of references to the appropriate columns in the hidden
sheet, or of calculated fields of my own devise that build on
the broker's data. (E.g., percent gain, annualized percent
gain, etc., etc.) (I even have the header-row title in the
calculated fields formatted differently -- different color
-- from the title in the linked ("imported," more or less)
columns/fields, so I can know at a glance which is my data and
which is the broker's.

I have a macro that refreshes the data sheet from it source and
fixes up the rows on my main, visible sheet to match the new
data.

I also use some conditional formatting to cross-check for data
validation. Sometimes the broker makes a mistake! E.g., if
Cost was $5,000.00 and proceeds were $5,555.55 but the Realized
Gain is listed as $555.53, my cell turns pink. If I bought 100
shares @ $50.00 but the Cost says $5123.45, that cell turns
orange. And so on.

Are these ideas at all helpful?

-dman-