House Temperatures

Recently, we had a scare with our furnace .. luckily, part was under warranty and we got it back up and running. However, having lived without heat for 2-3 weeks, I was super curious about .. how heat dissipated.. I am now aware of some sizing problems and return problems in the house. Specifically, i wanted to know if I should leave the Fan On or Auto, and how effective that was at equalizing temperatures between upstairs and downstairs. Answer: Its very effective. I answered these questions with the use of Govee temperature loggers, which can export CSV, tied into Excel.

The following is an overview minus the outside temperature, we’ll zoom into sections of this around certain things. It has two days, the first day was (mostly) fan ON, thermostat at 68; the second day (11pm 11/3) was fan on Auto.

The Gray line is the temperature at the vent – it shows when the heater kicks on and turns off. You’ll note that both days, outside sun warmed up the house to the point where heat was no longer being applied. About 10am-Midnight, amazingly. This is where multi-stage if we had it would be handy.

Upstairs is the green line, its where 3/4 grandkids sleep and play. What i didn’t know is that there’s a window AC unit in there, set to 70, that was turned on. First day, temperature trended upwards slowly, the AC barely kicked on (sharp downward spike). Second day – even during the night – AC kicked on several times, and a lot during the day. There’s a LOT of heat that just travels upstairs when the whole house fan is not running.

The Yellow and Orange are the two ends of downstairs. The Orange end does not have any returns, so whatever the A/C is doing, tends to accumulate there, especially when the fan is on. On the second day (I think) I tried to set up a house fan to help the air move out of that wing back into general circulation. It might have helped. Downstairs1, Yellow, btw, is directly below Upstairs, Green as far as these sensors go.

I also did a test of: What happens if I leave the door open, fully open, for 20 minutes, while its cold out. At the time it was about 35 degrees outside. Here’s a zoom in:

Zoomed in to Door Open event

The door was fully open for 20 minutes, from 9:10 to 9:30 on this graph. During that time, the thermostat-area dropped from 68 down to 66, and I can tell you that the floor of the room with the thermostat (also where the door was open) got down to 54. and the heat kicked on, HARD. The rest of the house actually warmed up from all this furnace activity – Upstairs went up the fastest. Once i shut the door, it took a good 40 minutes of heat before the thermostat was happy. It took maybe another 2 hours before all the extra heat worked its way through the house and things returned to normal. During this time, the Sun was out and was warming everything up, and so data gets a bit.. too many signals.

Just to be complete, here’s the full graph including the outside temperature.

Selling and Buying a Car in Louisville

For the last week I’ve been doing a lot of Car Analyses.  Some of it was trying to figure out how to judge one car over another;  another was visiting my history of cars, and determining satisfaction, and pinpointing my motives.  This is what I’ve figured out;  I’ll start with the most useful first:

My Experiences with Places to Sell or Buy a Car in Louisville

CarMax Will usually buy at the bottom end of KBB trade in value, unless <= 50k, then gives a decent price Will sell at above KBB Value.  *

* = better quality cars than we’ve bought anywhere else.

Cottage Car Sales Cottage Car Sales Better buy value than CarMax Sells at below KBB Value usually (but small inventory and some quirks)
Sam Swope Honda World image Best value for the Honda that we sold them Haven’t bought one from them myself. 

Disclaimer:  commentary above is based on 1 statistical sample in some cases and may not be significant.

In the end, we got $2000 more for our 2008 Honda Accord Coupe from Sam Swope Honda World than we would have at either Cottage or CarMax.  Terry Smith there was very professional, courteous, and prompt; no pressure of any kind; I would definitely do business with him again.

Evaluating the Worth of a Car To Me

When I was in the throes of trying to make a decision between a X and a Y, I needed some way to evaluate the cars.  Here are the tools that I found:

UsedAutoGraph.com
image
Mileage vs Sales Price
Active Listings vs Active Auctions
Overall value decline
How long does the car last
Popularity
http://www.edmunds.com/honda/civic/2010/reliability.html
image
Its somewhat buried in the Edmunds website, and really i should give a link to identifix, however, look at that URL.  I could very quickly look up any make/model/year and find years to avoid.
http://autos.jdpower.com/research/Honda/Civic/2010/ratings.htm
image
Another awesome URL, gave a quick overview of the quality of the car. 
Car Max Inventory Pictures of cars.
Mileage.
Feature availability.

I also used a formula like this:

  • Consider a 2010 Honda Civic EX for $16k with 42k Miles on it.
  • According to UsedAutoGraph, it holds its value well (should not die) till 100k, probably even viable to 200k.   Knowing myself, I’ll find a reason to upgrade, so lets say 150k miles instead.   Approx end sale price then is $5K.
  • So I would get 108k miles for approx $11k, yielding around 10 cents a mile from the purchase price.
  • at MPG of 25/36, I would get about 30mpg out of it, at $3.50/gallon yields  3.50/30 = 11 cents per mile gas price.
  • So the car would be about 21 cents per mile, for me.

I did this same analysis for the cars we had owned till now.  I always thought that the Honda Pilot that we bought was a complete gas hog.. averaging 15 mpg..  however, the sale price vs the buy price made it BETTER than the Ford Focus that we also had.  I would not have thought that.   (Then again, the Focus had an encounter with a Deer which dropped its resale considerably)

Picking through CarMax Inventories

We pretty much knew we were going to buy the next car through CarMax, or at least use a CarMax car as the baseline to compare against.  My general approach was:

  • Use them for test drives to figure out which car we want (year differences, trim level differences, lie down in the trunk of the car, etc)
  • Assume that we will NOT be buying the car from the local (Louisville) CarMax
    • This freed me from the necessity to make the decision NOW.
  • Search their inventory for the right mileage, color, options nationwide.  We could also look at the CarFax history (for free) and see where the car used to live (avoiding Florida due to Salt damage).
  • Spend $99-$299 and arrange a transfer of the vehicle to Louisville.
    • At this point, the car is locked in as mine.   But it might take a day to three weeks to execute.
    • This gives me time to reflect and really decide if this is the car for me.
    • Good thing, because that Ford Escape that I ordered.. ended up having second thoughts and choosing not to buy it.
    • To CarMax’s benefit – I cancelled the order fast enough, they had not loaded it on the truck, so they gave my money back (they didn’t have to).  (Well, i was transferring a different car, so they moved it to the second transfer).
  • The car hasn’t arrived yet; I’m in that reflecting place.   So I can’t say what happens next; but it will probably involve test driving, and then buying this car.

Filtering CarMax Inventories

They have a LOT of cars, with a LOT of options.  Here’s how I tackled that, in an Excel Spreadsheet:

Initial search:  No filter, just the make/model/year range, sorted by price, and or mileage.  Pull the first 10 into the spreadsheet.  Get a baseline, the corners of the grid. This is the least / most amount of money I would spend on that car type.   Chart it.   Bubble chart using a function to show age by bubble size.

Successive Searches:  I narrow it down by features, getting more and more selective.  I pull each of those into the spreadsheet, but as a different series.   I layer the series so that the more specific ones drown out the less specific ones.

Result:

image

Using this approach, I could easily see that if I wanted X set of options, the lowest miles for the price was at Y, but if I added Z option to it, then that drove the price up by Y2.      Ie, how much car am I willing to spend on.  Note that I inverted circle sizes, so that newer cars were bigger circles. 

imageNote that while filtering in CarMax, I really like how they show numbers next to features.  This helps me figure out what are “standard” options and/or are packaged with each other.  In this screenshot example,  (106) is standard across everything, while the Satellite Radio Ready is a much more selective.    I got pretty good at it, being able to choose 3-4 options that narrowed down the car to exactly what I was looking for.

Summary

Its been a crazy week.   Its been a fun research process.   In the end, I have a car on order that I cannot wait to get my hands on.   I even made a picture of it my desktop background. 

I hope some of these tools are useful to you in your future.     Enjoy! 

Excel C# Interop

imageI made a mistake.

I had a project in mind – to pull information from basecamp classic (todo items, with metdata embedded), massage it in Excel, and then upload it back to basecamp.   

I have always used Excel (or some equivalent spreadsheet) for project tracking – I never know which columns I need to add, when I need to highlight things in different colors, what kinds of charts I need to pull.  So the decision to bring the information down to Excel was an easy one.

That cost me 1.5 days of pain.

In retrospect, it would have been much easier to use EF Code First against .\SQLEXPRESS and use SSMS to do the bulk of my editing.   But, not having the time to re-tool, I pushed forward, and I did get it to work well enough to help me next week as I start work with that project…

Anyway, this is what I learned along the way:

Workbook.Save();
Workbook.Close(false);
Application.Quit();

The recipe for closing down the instance of Excel that had been open.   Without getting asked if I want to save.

Marshal.ReleaseComObject(sheets);

You have to do this for pretty much ever object you dink with, else the COM Excel wrapper stays open in the background. Reference: http://stackoverflow.com/questions/158706/how-to-properly-clean-up-excel-interop-objects). I haven’t fixed this yet.

Sheet.Cells[1,1].Value
Sheet.UsedRange.Cells[1,1].Value

Cells start at index 1, not 0.   UsedRange is faster than Cells.

var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";

You can use ACE to query spreadsheet data, however, type-coercion is sketchy.  As in, if you have no data, that long field will come back as string.    I ended up ditching that and reading/writing my lists of stuff directly from the worksheet so I could have direct control over the types:

Worksheet sheet1 = b.Worksheets["Tasks"];
t2 = sheet1.ReadListFromSheet<MyTask>();
sheet1.UpdateListToSheet(t2, "TodoItemId");
        public static List<T> ReadListFromSheet<T>(this Worksheet sheet) where T:new()
        {
            var nameToCol = GetNameToColumnDictionary(sheet);
            var colToSetter = GetColToSetterDictionary<T>(nameToCol);

            var result = new List<T>();
            var rowCount = sheet.UsedRange.Rows.Count; 

            for (int row = 2; row<=rowCount; row++)
            {
                var item = new T();
                foreach (var e in colToSetter)
                {
                    var col = e.Key;
                    var setter = e.Value;
                    var o = sheet.Cells[row, col].Value;
                    if (o == null) continue;
                    Type source = o.GetType();
                    var target = setter.GetParameters()[0].ParameterType;
                    try
                    {
                        object o2 = null;
                        if (target == typeof(bool?))
                        {
                            o2 = Convert.ChangeType(o, typeof(bool));  // bypass the null stuff
                        } else if (target == typeof(int?))
                        {
                            o2 = Convert.ChangeType(o, typeof (int)); 
                        } else if (target == typeof(DateTime?))
                        {
                            o2 = Convert.ChangeType(o, typeof (DateTime));
                        }
                        else
                        {
                            // this generic conversion seems to catch most everything
                            o2 = Convert.ChangeType(o, target);
                        }
                        setter.Invoke(item, new object[] {o2});

                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("Need to handle from type: {0}, value:{1} to setter type: {2}", source.FullName, o.ToString(), target.FullName);
                    }
                }
                result.Add(item);
            }
            return result; 
        }

        public static void UpdateListToSheet<T>(this Worksheet sheet, IEnumerable<T> lists, string pkColumnName)
        {
            pkColumnName = pkColumnName.Trim().ToLower();

            // Grab the list of columns
            var nameToCol = GetNameToColumnDictionary(sheet);

            // get a mapping from list member to column number / vice versa
            var colToGetter = GetColToGetterDictionary<T>(nameToCol);


            // grab the mapping of pk to row number
            if (!nameToCol.ContainsKey(pkColumnName)) throw new Exception("Could not locate primary key " + pkColumnName + " on sheet " + sheet.Name);
            var pkColumnNumber = nameToCol[pkColumnName];
            var pkToRow = new Dictionary<string, int>();
            var emptyRows = new List<int>();
            for (var r = 2; r <= sheet.UsedRange.Rows.Count; r++)
            {
                var pkValue = sheet.Cells[r, nameToCol[pkColumnName]].Value;

                if (pkValue == null)
                {
                    emptyRows.Add(r);
                }

                var test = pkValue.ToString();
                if (test == String.Empty) continue;
                pkToRow[test] = r;
            }

            // get a direct line to the getter of the pk
            if (!colToGetter.ContainsKey(pkColumnNumber)) throw new Exception("Could not locate primary column in DTO????");
            MethodInfo pkGetter = colToGetter[pkColumnNumber];

            // write the list contents into matching column names in the spreadsheet, updating as we go
            foreach (var item in lists)
            {
                var pkVal = pkGetter.Invoke(item, null).ToString();
                if (String.IsNullOrEmpty(pkVal)) continue; // inbound item has no PK

                // figure out which row to write to
                int rowToUpdate = -1;
                if (pkToRow.ContainsKey(pkVal))
                {
                    // row already exists .. update it
                    rowToUpdate = pkToRow[pkVal];
                }
                else
                {
                    // row does not exist .. append it
                    if (emptyRows.Count > 0)
                    {
                        rowToUpdate = emptyRows[0];
                        emptyRows.RemoveAt(0);
                    }
                    else
                    {
                        rowToUpdate = sheet.UsedRange.Rows.Count + 1;
                    }
                }

                // write it out
                foreach (var e in colToGetter)
                {
                    int col = e.Key;
                    MethodInfo getter = e.Value;
                    var o = getter.Invoke(item, null);
                    // any translations from .Net to Excel would happen here - none so far
                    sheet.Cells[rowToUpdate, col] = o;
                }
            }
        }

        #region Private

        private static Dictionary<string, int> GetNameToColumnDictionary(Worksheet sheet)
        {
            var nameToCol = new Dictionary<string, int>();
            for (var c = 1; c <= sheet.UsedRange.Columns.Count; c++)
            {
                var columnName = sheet.Cells[1, c].Value;
                if (!(columnName is string)) continue;
                if (String.IsNullOrEmpty(columnName)) continue;
                columnName = columnName.Trim().ToLower();
                nameToCol[columnName] = c;
            }
            return nameToCol;
        }

        private static Dictionary<int, MethodInfo> GetColToGetterDictionary<T>(Dictionary<string, int> nameToCol)
        {
            var colToGetter = new Dictionary<int, MethodInfo>();
            var properties = typeof (T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (var p in properties)
            {
                if (!p.CanWrite || !p.CanRead) continue;
                MethodInfo mget = p.GetGetMethod(false);
                if (mget == null) continue;
                var lowerPropName = p.Name.ToLower();
                if (!nameToCol.ContainsKey(lowerPropName)) continue; // not present in target xls
                colToGetter[nameToCol[lowerPropName]] = mget;
            }
            return colToGetter;
        }

        private static Dictionary<int, MethodInfo> GetColToSetterDictionary<T>(Dictionary<string, int> nameToCol)
        {
            var colToGetter = new Dictionary<int, MethodInfo>();
            var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
            foreach (var p in properties)
            {
                if (!p.CanWrite || !p.CanRead) continue;
                MethodInfo mset = p.GetSetMethod(false);
                if (mset == null) continue;
                var lowerPropName = p.Name.ToLower();
                if (!nameToCol.ContainsKey(lowerPropName)) continue; // not present in target xls
                colToGetter[nameToCol[lowerPropName]] = mset;
            }
            return colToGetter;
        }
  • Not yet memory-leak proof
  • Can only use a long as an id
  • Does not handle nullable columns gracefully

I’ll post more later on what I was trying to do.   I did finally get it to work.   Sketchy as it all is.  I love that word Sketchy… thank you Rutledge Wood!

Now I have to go pick up my race bib for a 10 mile race tomorrow.