filling records for missing dates of price series.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use MSexcel macros to collect prices data from various websites. The
results are tables that I link into an Access database. The tables (TblPrice1
… TblPriceN) all have the column headings:

Date Price1 Price2 ….PriceN

Each table comes from a different data source in a different country, all of
the tables have missing dates for holidays and weekends. Even within a table
for a given date there might be some missing individual prices.

I normalize the data into a table this the layout

Date PriceCode PriceValue

I have code that works for that.

I also want to fill in PriceValues for “missing“dates using the previous
price for all the prices. I don’t know if it would be easier to do this for
each table before I normalize it or after all the data is in one normalized
table.

Anyone have any ideas?

Thank for your help
 
From your description, it appears your "one normalized table" is missing one
of the pieces of data your original data has, i.e., the "country".

By the way, Access treats "Date" as a reserved word -- if your field name is
"Date", both you and Access will be confused. Use a different name for your
field, say, "PriceDate".

As for "filling in", be aware that your table apparently now stores "real"
data (actual data points you've gathered). If you begin interpolating
(inserting calculated values between real data points), you wouldn't be able
to differentiate what is real and what is calculated. Another approach
might be that you leave your data alone, as is, and use a query to calculate
the missing PriceDate values.

And this approach would keep you from storing calculated values in your
table -- rarely necessary or a good idea, since storing calculated values
means you'd have to re-calculate and re-store values each time a "real"
value was added and/or modified (e.g., correcting a typo).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Back
Top