Firmware for CMS2000 (Phoenixtec) solar invertor upgrade

With CMS in administration it is extremely difficult to find the latest firmware for CMS2000, I managed to find a copy from a Whirlpool forum member.

You can download it below;

CMS2000 Re Flash.rar

ModelParameter.zip

Below is an excerpt from the included procedure;

CMS 2000 Parameter Change.

  1. Apply DC supply only to the inverter. The unit will sit in ‘Grid Fault’ mode.

  2. Open Sunville Flash Tool ( or similar)

    image

  3. Click Setting & select your com port

    (if you do not know your com port settings. Contact your IT provider)

    image

  4. Click OK

  5. Click ‘load -> Open’ or click the folder icon to select the varied firmware version.

    image

  6. Select the file SV4KA.mot file

    image

  7. Click OK

  8. Click ‘flash’. If the correct coms port is selected the CMS 2000 will display FLASH. This process will take approx 2mins.

  9. Once the firmware is updated, you will get the following message, Click ‘OK’

    image

  10. Close the Flash Tool and open CMS ProControl. Select and enable the corresponding com port. (if you do not know your com port settings. Contact your IT provider)

    image

    image

  11. The serial number on the inverter will display.

    image

  12. Left click once to select the inverter. The bar at the top will go black.

    image

  13. Right click once and select parameter.

    image

  14. Password is: administrator

    The parameters will then be displayed.

    image

  15. Change the Vac-Max (V) to 270 volts, the highest permissible as per AS4777. Click Setting.

  16. Once your settings have changed. You will see the below message. Click OK

    image

  17. Disconnect the DC supply and allow the inverter to power down. Once discharged, turn on the DC and AC supply and connect to the grid. The current settings can be seen by double-clicking on the serial number in CMS ProControl.

    image

  18. Process Complete.

Model Parameters Error

Occasionally we have had the following error. If this occurs, you will not be able to change the parameter settings.

image

If this occurs, you can download the updated file from. http://carbonmanagement.com.au/support/ModelParameter.zip

Once this has been downloaded, you will need to extract the downloaded ModelParameter.txt file

& place it in c:\programfiles(x86)\CMS Pro Control & replace the existing file. Reopen CMS Pro Control & you should not see the error message.

PHP code/script to automatically purchase a Buy it Now (BIN) eBay item

Similar to my previous eBay bidding script, here is PHP code to automatically purchase a BIN item.

Note the script fails if immediate PayPal payment is required. Payment still needs to be performed manually.

Function takes item number only, so be careful with it.

	// MAIN BIDDING FUNCTION MAIN BIDDING FUNCTION
	// this function performs no checking on the input variables
	// $username  -  the eBay user name
	// $password  -  the eBay user password
	// $item      -  the eBay item number to buy
	// $link      -  itemlink with referral, can leave empty i.e. ''
	function place_bin($username, $password, $item, $link) {

		$cookies = dirname(__FILE__).'/cookies.txt';
		
		//set success as default false
		$success = false;
		$bid_success = false;

		$curl = curl_init();
		curl_setopt($curl, CURLOPT_SSL_VERIFYPEER, false);
		curl_setopt($curl, CURLOPT_USERAGENT, 'Mozilla/5.0 (Windows NT 6.1; rv:15.0) Gecko/20100101 Firefox/15.0.1');
		curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
		curl_setopt($curl, CURLOPT_FOLLOWLOCATION, true);
		curl_setopt($curl, CURLOPT_REFERER, $link);
		curl_setopt($curl, CURLOPT_COOKIEFILE, $cookies);
		curl_setopt($curl, CURLOPT_COOKIEJAR, $cookies);
		
		//query the sign-out page
		//curl_setopt($curl, CURLOPT_URL, "http://signin.ebay.com/ws/eBayISAPI.dll?SignIn&lgout=1");
		//$ret = curl_exec ($curl);
		
		//IMPORTANT		
		//query the sign-in page to set the cookies
		curl_setopt($curl, CURLOPT_URL, 'http://signin.ebay.com/aw-cgi/eBayISAPI.dll?SignIn&campid=5337161990&customid=7');
		curl_exec ($curl);
		
		//query the referal link page
		if ($link) {
			curl_setopt($curl, CURLOPT_URL, $link);
			$ret = curl_exec ($curl);
		}
		
		//sign-in
		curl_setopt($curl, CURLOPT_URL, "http://signin.ebay.com/aw-cgi/eBayISAPI.dll?MfcISAPICommand=SignInWelcome&siteid=0&co_partnerId=2&UsingSSL=0&ru=&pp=&pa1=&pa2=&pa3=&i1=-1&pageType=-1&userid={$username}&pass={$password}");
		$ret = curl_exec ($curl);
		if(curl_errno($curl)){
			ebaylog('Curl error: ' . curl_error($curl));
		}
		if (!$ret) {
			$ret = curl_exec ($curl);
			if(curl_errno($curl)){
				ebaylog('Curl error: ' . curl_error($curl));
			}
			if (!$ret) {
				$ret = curl_exec ($curl);
				if(curl_errno($curl)){
					ebaylog('Curl error: ' . curl_error($curl));
				}
			}
		}
		
		if (strpos($ret, '"loggedIn":true') === FALSE) {
			if (preg_match('%<b class="altTitle">(.*)</b>%', $ret, $regs)) {
				$err = $regs[1];
				ebaylog("\"{$err}\"");
				if (strpos($err, 'The alerts below') === 0) {
					ebaylog("{$item}: 'The alerts below' found, successful");
					//set it to succes
				}
			} else {
				ebaylog("{$item}: Failed signing in");
				if (preg_match('%<font face=".*?" size="3"><b>(.*?)</b></font>%', $ret, $regs)) {
					$err = $regs[1];
					ebaylog("\"{$err}\"");
				}
				
				//test_write($ret);
				goto end;
			}
			
			
		} else {
			ebaylog("{$item}: Success signing in");
		}
		
		
		//place the initial bin
		curl_setopt($curl, CURLOPT_URL, "http://offer.ebay.com/ws/eBayISAPI.dll?BinConfirm&item={$item}&quantity=1&campid=5337161990&customid=7");
		$ret = curl_exec ($curl);
		if(curl_errno($curl)){
			ebaylog('Curl error: ' . curl_error($curl));
		}
		if (!$ret) {
			$ret = curl_exec ($curl);
		}

		
		if (preg_match_all('/(?:value="([-0-9a-zA-Z]*)" *)?name="stok"(?: *value="([-0-9a-zA-Z]*)")?/', $ret, $regs)) {
			$stok = $regs[1][0];
		} else {
			//Failed to get 'stok' value
			//try and determine why
			
			//check if immediate paypal checkout required
			if (preg_match('%<p\W*>\W*(.*)</p>%i', $ret, $regs)) {
				$err = $regs[1];
				if (stripos($ret, "You're almost done!") === 0) {
					ebaylog("Requires immediate PayPal payment.");
					//set it to success
					$success = true;
					goto end;
				} else {
					test_write($ret);
				}
			
			} else if (preg_match('%<div class="statusDiv">(.*?)</div>%', $ret, $regs)) {
				$err = $regs[1];
				ebaylog("'{$err}'");
				//if string starts with "Enter US $0.41 or more"
				if (stripos($err, 'Transaction Blocked') === 0) {
					ebaylog("{$item}: 'Transaction Blocked' found, aborting");
					//set it to success
					$success = true;
				} else {
					test_write($ret);
				}
				
			} else if (preg_match('%"\d*" - Invalid Item</div>%', $ret)) {
				ebaylog("{$item}: 'Invalid Item' found, aborting");
				test_write($ret);
				//set it to success
				$success = true;
			} else if (preg_match('%<div class="subTlt"><ul class="errList"><li>(.*?)</li></ul></div>%', $ret)) {
				ebaylog("{$item}: 'no longer available' found, aborting");
				test_write($ret);
				//set it to success
				$success = true;
			} else if (preg_match('%id="w\d-\d-_msg".*?>(.*?)</span>%', $ret, $regs)) {
				ebaylog("'{$regs[1]}'");
			} else if (preg_match('%<div\s+class\s*=\s*"(?:errRed|errBlk|errTitle|statusDiv)"\s*>(.*?)</div>%i', $ret, $regs)) {
				ebaylog("'{$regs[1]}'");
			} else {
				//don't know why so log the page
				ebaylog("{$item}: Failed to get 'stok' value");
				test_write($ret);
			}
			goto end;
		}
		
		if (preg_match_all('/(?:value="([-0-9a-zA-Z]*)" *)?name="uiid"(?: *value="([-0-9a-zA-Z]*)")?/', $ret, $regs)) {
			$uiid = $regs[1][0];
		} else {
			ebaylog("{$item}: Failed to get 'uiid' value");
			goto end;
		}
		

		if ($stok && $uiid) {
			ebaylog("{$item}: Success placing initial bid");
		} else {
			ebaylog("{$item}: Failed placing initial bid");
			goto end;
			
		}
		
		//confirm the bid
		$temp = "http://offer.ebay.com/ws/eBayISAPI.dll?MfcISAPICommand=BinConfirm&quantity=1&mode=1&stok={$stok}&xoredirect=true&uiid={$uiid}&co_partnerid=2&user={$username}&fb=0&item={$item}&campid=5337161990&customid=7";
		curl_setopt($curl, CURLOPT_URL, $temp);
		$ret = curl_exec ($curl);
		if(curl_errno($curl)){
			ebaylog('Curl error: ' . curl_error($curl));
		}
		if (!$ret) {
			$ret = curl_exec ($curl);
		}
		
		if (stripos($ret, 'Commit to buy') !== FALSE) {
			ebaylog('Trying again');
			$ret = curl_exec ($curl);
			if(curl_errno($curl)){
				ebaylog('Curl error: ' . curl_error($curl));
			}
			if (!$ret) {
				$ret = curl_exec ($curl);
			}
		}
		
		//perform a number of tests to determine if the bid was a success
		$bid_success = true;
		if (stripos($ret, 'Please pay now to complete your purchase.') === FALSE) {
			$bid_success  = false;
			ebaylog("{$item}: Failed placing final bid");
			//try and determine why
			if (preg_match('%<div\s+class\s*=\s*"(?:errRed|errBlk|errTitle|statusDiv|title)"\s*>(.*?)</div>%i', $ret, $regs)) {
				$err = $regs[1];
				ebaylog("'{$err}'");
			} else {
				//we don't know why it failed so write the data
				test_write($ret);
				ebaylog($temp);
			}
		}
		
		if ($bid_success) {
			ebaylog("{$item}: Success placing final bid");
			$success = true;
		}
		
		end:
		
		//close the curl session
		curl_close ($curl);
		
		if ($success) {
			ebaylog("{$item}: Success: {$username}");
		} else {
			ebaylog("{$item}: Failure: {$username}");
		}
		
		return $success;
	}

Misc functions below;

	//for testing
	function test_write($out) {
		$fh = fopen(dirname(__FILE__).'/'.date('Y-m-d H-i-s').'.html', 'w');
		fwrite($fh, $out);
		fclose($fh);
	}

	//simple logging function
	function ebaylog($msg) {
		$msg = "{$msg}\r\n";
		echo $msg;
		$fh = fopen(dirname(__FILE__).'/ebay-log.txt', 'a');
		fwrite($fh, date('Y-m-d H-i-s')." {$msg}");
		fclose($fh);
	}

How to calculate comparison interest rate in Excel

Searching for how comparison interest rates are calculated, I found an article on Tomorrow Finance, but unfortunately a lot of the numbers provided no insight into how they were calculated. I hope to break it down further and explain how to calculate comparison interest rates with Excel. The Excel formula can be quite tricky and the parameters vague, so I will go through each of the formula as well.

  • rate – the interest rate of the loan annually equal to 4.63% (the current rate most banks are offering as of today)
  • principle – the loan amount always equal to $150,000 when calculating a comparison rate.
  • period – the period in years always equal to 25 years when calculating a comparison rate.

Payments for a loan

Firstly, calculate the monthly payments for a loan based on constant principle payments and a constant interest rate. The key here is to convert the interest rate to a monthly interest rate and the number of periods also in months.

payment = PMT(rate/12, period*12, principle)

In this case payments equals -$844.86 per month and includes principle and interest. The figure is negative since it is an outgoing.

Total interest payment

total payment = payment * 12 * period

Since the monthly payments are all the same, the total payments is simply the monthly payment multiplied by 12 months by 25 years, this equals -$253,456.66.

total interest = total payment + principle

The total interest is imply the total payment (which is negative) plus the principle, this results in a total interest only of -$103,456.66.

A quicker way to calculate the cumulative interest over the lifetime of a loan is;

total interest = CUMIPMT(rate/12, period*12, principle, 1, period*12, 0)

Additional fees

Next the additional fees over the lifetime of the loan are added, usually these are annual service fees, with nab there is a -$395 annual service fees. The total fee over the life of the loan would be 25 years multiplied by -$395 which equals -$9,875.

total outgoings = total interest + total fees

The total outgoings over the life of the loan would be the total interest and total fees, this would equal -$113,331.66.

outgoings = total outgoings / 25 /12

To get the outgoings per month divide by 300 to get -$877.77 per month.

Comparison rate

Finally, the comparison rate can be calculated;

comparison rate = RATE(period * 12, outgoings, principle)*12

The rate is per month which is multiplied by 12 to get an annual comparison rate, in this case equal to 0.050101482 or 5.01%.

Download the WorkBook how-to-calculate-comparison-interest-rate-in-excel.xlsm.

Export Android contacts contacts2.db to vCard vcf on Windows

The other day I need to restore some contacts on an Android phone, I had Titanium Backup files, though restoring the contacts through there didn’t seem to work.

I searched for a solution but there wasn’t really anything for Windows to restore the contacts so I developed my own VBA script.

I will assume you already have a backup contacts2.db file, it is found the zip file com.android.providers.contacts.

Firstly, download SQLite Manager add-on for Firefox, run it, Connect Database contacts2.db. Under Tables, right-click data table and chose Export Table click OK and save as data.csv.

Download convert-contacts2.db data-to-vcard-vcf.xlsm, open, and enable macros if necessary. Run the macro convertDatatoContacts and select the data.csv file.

You should now have a list of contacts, delete any contacts that you don’t want to import by deleting the whole row. When done run the macro and a vCard.vcf file will be created in the same folder as the Excel WorkBook.

Copy vCard.vcf to the root  directory of your Android phone. On the phone enter Contacts, Settings, Import/Export and Import from storage. The contacts will be imported.

 

Remove Hyundai Tucson MP3-01 radio with removal tool

I recently upgraded my factory default radio on Hyundai Tucson 2005, radio model MP3-01, to an aftermarket head-unit with Bluetooth.

To remove the unit requires a special tool which is described as “Tool for removal of radio in Hyundai Terracan, equivalent to part 99260-426-0”. Below are details on the tool’s dimensions, I used 0.5mm aluminum which was easy to work with, but not very strong, it did the job though.

Download a printable template of the tool.

Histogram with normal distribution overlay in Excel

This tutorial will walk you through plotting a histogram with Excel and then overlaying normal distribution bell-curve and showing average and standard-deviation lines.

To produce my random normal samples I used VBA function RandNormalDist by Mike Alexander. I created samples with a mean of 100 and standard deviation of 25, function RandNormalDist(100, 0.25).

The actual mean and standard deviation was 100.84 and 27.49 respectively.

Normally distribution

The samples can be checked to confirm normally distributed by comparing the mean, median and mode which should all be equal.

Mean 100.84
Median 99.5
Mode 89

Histogram

The first thing to do is produce the histogram. This is done by creating bins of a certain width and counting the frequency of the samples that fall in each bin.

Start by calculating the minimum (28) and maximum (184) and then the range (156). Using Sturges’ formula the number of bins is 9, using the square root method the number of bins is 15.

To get a bin width, divide the range (156) by the number of bins (9) which results in 17.33, round this up to an even 20 to produce nice round bin widths.

Set up the bins starting at the minimum and ending at the maximum, using the Excel FREQUENCY function to determine frequency in each bin. The FREQUENCY Function must be entered as an array (ctrl-enter).

Bin Frequency
0 0
20 0
40 1
60 12
80 32
100 56
120 51
140 28
160 19
180 0
200 1

Using a column chart a histogram can be produced.

Histogram using standard column chart

Histogram using Scatter Chart

Overlaying a normal curve is a little trickier, firstly, the above column chart can’t be used and the histogram must be produced using a scatter chart.

Select the data and produce a scatter chart with smooth lines.

Scatter chart as histogram

Select the chart and click on the ribbon menu, Layout, then Error Bars and then More Error Bars Options. Select Display Direction Minus, End Style No Cap and Error Amount Percentage 100%.

Scatter chart as histogram format error bars

This will produce a scatter chart with the following error bars.

Scatter chart as histogram with error bars

Increase the Line Style Width so that it starts looking like a histogram with no gaps.

Scatter chart as histogram using error bars

Finally, remove the scatter chart line.

Scatter chart as histogram using error bars finished

Normal Curve

For the normal curve the points need to be created first. The bell curve looks nice when it covers the full 6 standard deviations. 100 points will be created for a nice smooth curve.

Multiply the standard deviation (27.49) by 6 to get 164.96, divide by 100 to get an increment of 1.6496. Starting at minus 3 standard deviations (equal to the mean minus 3 standard deviations (18.36)) increment the value by 1.6496 all the way up to positive 3 standard deviations(183.32). There will actually be 101 total points.

Now for each of those points the normal distribution shall be calculated using Excel’s NORMDIST function. The first parameter is the values we calculated, the second the mean, the third the standard deviation and the last should be FALSE as we don’t want cumulative (NORMDIST(Q1,100.84,27.49,FALSE)).

If you plot the data you will notice a very short normal distribution curve, barely visible as a bell curve due to differences in scale. For our sample of 200 points with bin width of 20, each sample represents a square of 20 by 20. So the total area of our histogram is 200 by 20 which is 4000. The normal distribution has a total area of 1, so the normal curve must be scaled by 4000. And this produces a nice bell-shaped normal curve over the histogram.

Scatter chart as histogram with normal curve

Histogram correction

You may notice that the histogram and bell curve is a little out of sync, this is due to the way the bins widths and frequencies are plotted. If you have a bin width of 20, and the bin value is 40, the corresponding frequency is all values between 20 and 40. When you plot this value on a scatter chart, the centre of the bar is at 40 and the bar width being plus and minus half the bin width (10), which is 30 to 50 respectively. That’s why the histogram looks shifted to the right.

To fix this, create a temporary fixed bin that has half the bin width (10) subtracted from it and use this when plotting the histogram.

Bin Frequency Corrected Bin Frequency
0 0 -10 0
20 0 10 0
40 1 30 1
60 12 50 12
80 32 70 32
100 56 90 56
120 51 110 51
140 28 130 28
160 19 150 19
180 0 170 0
200 1 190 1

Scatter chart as histogram with normal curve corrected

Mean and Standard Deviation

Since it is a scatter chart, it is possible to add additional indicators including mean and standard deviation lines.

Simply produce a single line segment from 0 to the height of the bell curve using the previous NORMDIST function.

-3sigma 18.35943 0
18.35943 0.644784
-2sigma 45.85295 0
45.85295 7.855081
-1sigma 73.34648 0
73.34648 35.20403
mean 100.84 0
100.84 58.04164
1sigma 128.3335 0
128.3335 35.20403
-2sigma 155.827 0
155.827 7.855081
3sigma 183.3206 0
183.3206 0.644784

Scatter chart as histogram with normal curve, mean and standard deviation

Tidying up the colours results in the following final histogram with overlaid normal curve and mean and standard deviation indications.

Scatter chart as histogram with normal curve, mean and standard deviation final

Download the Excel workbook here.

Free copy of The Building Code of Australia (BCA)

What a free copy of the building code of Australia (BCA), the Australian Government has made it public.

In order to promote public education and public safety, equal justice for all, a better informed citizenry, the rule of law, world trade and world peace, this legal document is hereby made available on a noncommercial basis, as it is the right of all humans to know and speak the laws that govern them.

National Construction Code Series 2012 VOLUME ONE HTML
National Construction Code Series 2012 VOLUME TWO HTML
National Construction Code Series 2012 VOLUME ONE PDF
National Construction Code Series 2012 VOLUME TWO PDF

Automatically start and run LabVIEW VI

Here is an AutoIT script and executable that takes a LabVIEW VI path, opens the VI and then runs the VI.

Script and download below.

#include <File.au3>

If $CmdLine[0] > 0 Then

; assume first parmaeter is the file and path of the VI
Local $sFilePath = $CmdLine[1]

Local $sDrive = "", $sDir = "", $sFilename = "", $sExtension = ""
Local $aPathSplit = _PathSplit($sFilePath, $sDrive, $sDir, $sFilename, $sExtension)

; generate the window title
Local $sTitle = $sFilename & $sExtension & " Front Panel"

; run the VI
ShellExecute($sFilePath )

; wait till the VI opens
WinWaitActive($sTitle)

; send CTRL-R to start to RUN
Send("^r")

EndIf

Open and Run LabVIEW VI AutoIt
Open and Run LabVIEW VI EXE

Use as follows;

open-run-labiew-vi.exe "C:\Users\UserName\Desktop\VIName.vi"