New project -- how to handle weather data

For Christmas, I got one of those inexpensive home weather station things. It collects data from sensors in the yard, then stores that information in a .csv file. I can pull this data onto my computer and do stuff with it, if I want. I could:

plot data in charts to show trends with time
analyze data for statistical information (averages, max, min, etc.)
or whatever I want and can figure out how to do.

My question for the group at this stage is -- how to deal with this data for maximum future flexibility and interest?

The unit stores data every 12 minutes (120 readings/day, about 45,000 readings/year), so I will be well within Excel's limitations for a long time if I choose to import the data into Excel. But is Excel really the best place to store this information for future access?

I'm not very proficient in pivot tables, but a pivot table seems like one good way to extract information from the broader database. But pivot tables have certain limitations (such as when you want to use a scatter plot to visualize the data).

Filters can be used to extract subsets of the data.

I could download an open source database program (like MySQL or something like that), and use that as the main point for accessing and extracting data.

I guess I am seeking for some input from those who are more proficient in some of the "database type" tools as I try to set something up to access this data, before I get myself committed to any specific approach to storing and retrieving data. How would you set this up for accessing and analyzing a .csv database file like this? What considerations would you take into account? Would you use Excel exclusively, or would you prefer a separate database program?

Just thinking out loud here, but if you at least get it INTO excel, you then have it in a state where the manipulation options are greatly increased?

As one possible approach, you are correct. And getting it into Excel in its entirety is not difficult: Data -> Get external data from text --> follow text import wizard --> Data is now in Excel, separated into columns, and easily updated as new data is added to the file.

One of the questions I am looking at right now is -- do I want to pull all of the data into Excel, or will it be better to keep the entire database outside of Excel, and only import the portions that are needed on a given day? Excel has several database utilities, even though it is not really a full-fledged database program. Perhaps, once I become proficient in these database tools, Excel will be perfectly adequate. It would then be about me learning these tools. Because I am much more comfortable with Excel as a work environment, this approach certainly has a certain appeal.

I am excel-biased as well, and there are a host of tools for manipulating data

I use access to store large quantities of .csv data. (It may not sound like much I know) Every month I download between 2500 and 3000 records 85 columns wide and create statistical reports, analysis general reports from there. I'm partial to access as a storage tool but it can be finicky (at least older versions) so I maintain a backup DB though the 2010 version I use has been quite stable compared to older versions. It contains a lot of text and even memo data and the flexibility to manipulate access is very valuable to me. Plus I like that I don't have to know SQL to use it but I'm still able to look at the SQL background and I have learned some basics. FWIW.

I don't have access to Access, or other database program. I think I will stick with Excel as my main data storage container, since that is what I know.

Now, I guess, it is time to learn something about Excel's pivot and database tools.

First question, then. After importing the data into the spreadsheet, what if anything do I need to do to make the source data more amenable to a pivot table? If I want to get an average for each calendar day, for example, do I need to add columns that separate out date and time from the time stamp?

Years ago I developed some analysis tools to analyse telephone bills for my clients - essentially, I had a template into which I could drop the call data each month (it first had to be translated into a standard format for different suppliers), adjust the named ranges to suit the number of calls, and then the rest was all formula-driven. I had pre-defined reports on different sheets which gave me different views of the data (tables and graphs). I looked into using pivot tables, but found that they did not give me the analysis in the forms I wanted.

So, instead of deciding at the outset that you want/need to use pivot tables, ask yourself first what you want to do with the data. I presume you have temperature variations, wind speed, rain etc. as variables, so sit down and work out how you would like to analyse these things and what is the most suitable way of displaying them. You might want to compare the current day (or yesterday) with the previous day, or monthly average to date. Maybe in the future compare the current month with the same month in the previous year, or current quarter with previous quarter etc.

Once you have a clearer idea of what you want to achieve, then you can consider how you might tackle the task - functions like SUMIF(s)/AVERAGEIF(s)/SUMPRODUCT etc. and MAX/MIN as array formulae can get you a long way.

Hope this helps.

Pete

I looked into using pivot tables, but found that they did not give me the analysis in the forms I wanted.

This is kind of where I am at, now, too. I tried playing around with a pivot table, but there just aren't that many "analyses" that the pivot table can do.

For example, one thought I had was to create a box and whiskers diagram for each day. Unless there are more "value field settings" than I could see, the pivot table could only calculate max, min, average, and standard deviation for each day, but it would not be able to calculated median, quartiles, or percentiles. But, with a fairly simple PERCENTILE(IF(...),A$1) type formula, I could calculate each desired percentile and make a nice box and whiskers plot.

I did use the pivot table for one thing. Since I did not know any other way, the pivot table was an easy way to generate a list of dates from the raw data. I went ahead and added a column to pull the date part of the date/time stamp (=int($A2)). Then, using this column as the row labels in the pivot table, I could easily get a list of unique dates.

PT's can do a lot of the grunt work, and sometimes they can be used to show data in a presentable way, but more often than not (for me, anyway), they just dont have the ability/flexibility to present data in the way I would want to. We have some real PT wizz's here, maybe they can add in here.

My go-to for this kind of thing has always been formulas and table that I have built