In <
[email protected]>,
dk_ said:
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-
====================================================================