2009-10-22

Simple Ruby and Bloomberg

I have been looking for very simple Bloomberg code.  I have used this post from bgimpert on Wilmott as a starting point.  I have copied at the bottom of this post in case it disappears.

My very simplest code is showing both historical data and static data is:

require 'win32ole'
bb =WIN32OLE.new('Bloomberg.Data.1')
bb.BLPGetHistoricalData2(["SPX Index"],["PX_LAST"],Time.now,"USD")

bb.BLPSubscribe(["EUE LN Equity"],["ID_ISIN"])

which returns the latest date and value pair.

Digging around is helped by inspection eg

bb.ole_get_methods

bgimper post on Wilmott
Or in Ruby, with no worries about dependencies or references or DLL's:
require 'win32ole'
class Bloomberg
ONE_YEAR = 60 * 60 * 24 * 365.25
DAILY_PERIODICITY = 1
WEEKLY_PERIODICITY = 6
MONTHY_PERIODICITY = 7
ANNUAL_PERIODICITY = 9
OMIT_NON_TRADING_DAYS = 0
WEEK_NON_TRADING_DAYS = 64
ALL_CALENDAR_NON_TRADING_DAYS = 128
BLOOMBERG_HANDLES_NON_TRADING_DAY_VALUE = 0
PREVIOUS_DAYS_NON_TRADING_DAY_VALUE = 256
SHOW_NO_NUMBER_NON_TRADING_DAY_VALUE = 512
def initialize
@bloomberg = WIN32OLE.new('Bloomberg.Data.1')
@bloomberg["ActivateRealtime"] = false
@bloomberg["Periodicity"] = DAILY_PERIODICITY
@bloomberg["DisplayNonTradingDays"] = OMIT_NON_TRADING_DAYS
@bloomberg["NonTradingDayValue"] = PREVIOUS_DAYS_NON_TRADING_DAY_VALUE
end
def get_stock_price_history(ticker, start_time = Time.now - ONE_YEAR)
prices = @bloomberg.BLPGetHistoricalData2(["#{ticker} UN Equity"], ["PX_LAST"], start_time, "USD")
prices.map do |price_ar|
day_s, price = price_ar
if price_ar.empty?
nil
else
[DateTime.parse(day_s), price.to_f]
end
end.compact
end
end
bb = Bloomberg.new
bb.get_stock_price_history("GE").each do |ar|
day, price = ar
puts "day = #{day}, price = #{price}"
end

2009-09-18

Opening an Excel file that needs updating

The syntax for this was not obvious to me but is shown here by David Mullet.  So here is a worked example for Excel and updatelinks when opening a spreadsheet.

#----------Open excel sheet that needs updating
require 'win32ole'
xl = WIN32OLE.new('Excel.Application')
xl.Visible = true
#workbook = xl.Workbooks.Open('C:\Flash.xls')
workbook = xl.Workbooks.Open( { 'FileName' => 'C:\Flash.xls', 'UpdateLinks' => 3 } )

Creating Windows Exe

Just to note that I have migrated to using OCRA for packaging Ruby.  It is a bit slow unpacking and starting compared to EXERB but is more widely supported.  I had a problem packaging Watir which was resolved with OCRA.  It works really nicely and will work with Ruby 1.9. 

I used to use rubyscript2exe but that has ossified although it had wonderful documentation.

2009-09-10

IP utilities

Just came across www.robotex.com which has a very nice DNS search and reporting system.

2009-09-03

Exerb Reference Recipes for Ruby translated from Japanese by Google and tidied by me

Exerb Project 4.20
Welcome to "Reference Recipes
Reference Recipes

Contents


    1. Overview
    2. YAML format
          1. Array
          2. Hashing
          3. Combination
    3. Recipes for file blocks
          1. General block
          2. Path blocked
          3. Resource block
          4. File blocks

1. Overview


Recipe file (.EXY) is a text file describing the various files needed to generate the executable. YAML (YAML Ain't Markup Language) format is used and the character encoding is UTF-8.

The simplest example of a recipe file is as follows:

general:
   startup: hello.rb
file:
   hello.rb:

2.YAML format simple introduction

In YAML, and indent using some symbols to represent data structures. The data structure is a broad, there are two types of arrays and hashes.
With the following example to describe the symbol more ,"#=>", Ruby in the data in both the above.

2.1. Array


Array is then written in the form below.
- Value
Described as follows by the representation of the array.
- Array
- String
- 123
# => [ "Array", "String", 123]
Ruby is treated as an object on the Array.

2.2. Hashing

The hash is then written in the form below.
Key: value
Described as follows by the representation of the hash.
Type: Hash
String: ABC
Number: 123
# => ( "Type" => "Hash", "String" => "ABC", "Number" => 123)
Ruby is treated as an object on the Hash. Therefore, the order of the keys will not be saved.

2.3. Combination


Arrays and hashes can be combined, respectively. By indentation indicates the parent-child relationship. Spaces to indent, you can use a tab character, recommends that you use two letter spaces.
Combination of cases among an array is as follows.
--
   - 1x1
   - 1x2
--
   - 2x1
   - 2x2
# => [[ "1x1", "1x2"], [ "2x1", "2x2"]]
Hash together a combination of cases is as follows.
Key1:
   Type: String
   Value: ABC
Key2:
   Type: Number
   Value: 123
# => ( "Key1" => ( "Type" => "String", "Value" => "ABC"),
      "Key2" => ( "Type" => "Number", "Value" => 123))
Combination of hash and array is as follows.
String:
   - A
   - B
   - C
Number:
   - 1
   - 2
   - 3
# => ( "String" => [ "A", "B", "C"], "Number" => [1, 2, 3])

3. Recipe File Blocks


The following recipe file blocks (hash structure) is composed. Order description of each block is optional, we recommend that you write in the following order.
    1. General block (required)
    2. Path blocked
    3. Resource block
    4. File block (required)
Please use UTF-8 encoded to include other languages such as Japanese.

Block 3.1.general (Required)


Specify the basic information about the executable file to be generated. This block is required.
general:
   startup: foo.rb
   output: bar.exe
   core: cui
   kcode: sjis
startup key when you start the executable, you first run the Ruby script file name. This key is required.
key output specifies the output file name of the executable file to be generated. If omitted, the file name is used recipes. Recipe file extension "exy" If the extension "exe" file name substitution, and otherwise ". Exe" file that uses the name appended. If the output file name is specified in the command options are given priority by the command options.
core key is used to generate the executable file that specifies the core Exerb. Defined name, specify the name or file. If the core is specified in the command options are given priority by the command options.
kcode key, Ruby script to specify the character code. corresponds to the ruby command-K option. If the character code specified in the command options are given priority by the command options. Letter codes, please specify the following one.
Means the value specified
Any none (default)
euc EUC
sjis Shift-JIS
utf8 UTF-8

Block 3.2.path


Ruby script to specify the search path for libraries and other extensions. Absolute path to relative path described in the recipe file or directory exists.
path:
   - .. \ Lib \ so
   - C: \ ruby \ lib
   - C: \ ruby

Block 3.3.resource


Specifies the information to generate the executable file resources.
This block specifies the block and icon resources icon of the executable file, which specifies the version is the version information resource blocks.

Block 3.3.1.icon


Specifies the icon resources. If this block is omitted, the default icon is used that is included in the core. Multiple sizes, you can specify the icon for the number of colors, other than to specify the icon resources Meinaikon not.
resource:
   icon:
     --
       width: 16
       height: 16
       color: 4
       file: file.ico
key width, the number of pixels in the horizontal direction of the icon (W) is specified.
key height, the number of vertical pixels of the icon (height) is specified.
key color, you specify the number of bits of color depth of icons. For example, if 16-bit color 4.
key file, specify the above three files that contain a single icon for the specified key.複数のサイズ、色数が含まれているアイコンファイルを指定することができますが、アイコンリソースとして使用されるのは、上記3つのキーで指定したアイコンのみです。

Block 3.3.2.version


Specifies the version information resource. If this block is omitted, the default will use the information contained in the version of the core.
resource:
   version:
     file_version_number: 1.2.3.4
     product_version_number: 5.6.7.8
     comments: comment
     company_name: Company
     legal_copyright: Copyright Information
     legal_trademarks: Trademark Information
     file_version: File Version (text)
     product_version: product version (string)
     product_name: Product
     file_description: description of the file
     internal_name: Internal name
     original_filename: official file
     private_build: Puraibetobirudo Information
     special_build: Supesharubirudo Information
file_version_number key, product_version_number the keys, file versions, respectively, separated by a dot-product version 4 specifies two numbers. Each figures, can be used from 0 to 65535 range.
Other key specifies the contents of each string. If you use Japanese text is a recipe file to UTF-8 encoding please.

Block 3.4.file (Required)

Specify the files that need to be included in the genereated EXE file. This block is required.

file:
   foo.so:
     file: foo / foo.so
     type: extension-library

The key name to specify a file name. This file name is called the internal file name, require reading the file is used by the method. The internal file name to be compared as a single string, including path separator character, "require" .. / foo / bar.so "" Please be careful and not interpret it correctly. The above method calls require ".. / foo / bar.so" internal file names that are searched.
key file specifies the actual file name. If the same internal file name, or you can search from the search path can be omitted.
key type specifies the type of file. If omitted, the type is determined based on the file extension.

Describes the extension type
. rb script
(Ruby script) Ruby script written text. require can be read at.
. so extension-library
(Library extensions) C library extensions written in languages such as (dynamic). require can be read at.
. dll dynamic-library
(Dynamic) dynamic link library used by the extension. If requested by the extension library is loaded automatically. Can not be loaded explicitly.
. res resource-library
(Library resources) that contains only dynamic resource. Exerb resource data can be read using a runtime module.
. dat data
(Data) binaries, images, data files and databases. Exerb can be read using a runtime module. Can not write.
Runtime module-Class Reference, please refer to.


Copyright (C) 2001-2006 All rights reserved.

Exerb Command reference for Ruby translated from Japanese by Google and tidied by me

Exerb Project 4.20

Welcome to "Command Reference

Command Reference

Contents

  1. Overview
  2. command exerb
    1. Parameters
    2. Options
  3. command mkexy
    1. Parameters
    2. Options

1. Overview

Exerb to include two single command. Exerb command to generate an executable file to generate a recipe file mkexy command.

2. Exerb command

Ruby script file, or specify a recipe file to generate an executable file.

exerb [option] (Ruby script file | files) recipe

1. Parameter

Ruby script to specify a file name or recipe. Can not specify multiple file names. The file name extension "rb" as if the Ruby script file, otherwise it is treated as a recipe file.

Ruby script file is specified, Ruby will generate an executable file that contains only the script. Ruby single software consists of a simple script that will be used to convert the executable.

Recipe file is specified, the executable file is generated according to the information contained in the recipe. Multiple Ruby script, a software library that consists of extended and used to convert the executable. File formats Recipes Reference, please refer to the recipe.

2. Options

You can specify the following options. If the same item at the specified recipe file and command options, command option will take precedence.

Options
Description

-c
-- corename
Specifies the name defined in the core. If -C option is specified at the same time,-C is the preferred option.

-C
-- corefile
Specifies the core file path.  -c option is specified at the same time,-C is the preferred option.

-o
-- outfile
Specifies the output file name.

-k
-- kcode
Specifies the character code. none, euc, sjis, utf8 Please specify one of them.

-a -a
--archive
Generates only archive. Executable file is not generated.

-v -v
-- verbose
Redundant information to display.

-e -e
-- execute
Run the executable file generated.

-i
-- config
Displays various settings.

-V
-- version
Displays version information.

-h
-- help
Displays the help message.

3. Mkexy command

Ruby is running the script automatically generates a recipe file. Please start one of the following ways.

mkexy [option] Ruby Script File Parameters

ruby-r exerb / mkexy [option] Ruby Script File Parameters

1. Parameter

"Ruby script file" in the recipe file is generated for the specified Ruby script. Ruby is running the specified script file is generated at the end of recipes.

"Parameters" in the recipe file to be created to specify the arguments passed to the Ruby script.

2. Options

Will ruby option to accept commands.

Copyright (C) 2001-2006 All rights reserved.

Creating Ruby exes using FX ruby

Just wanted to note this post for creating a single exe.  First using exerb  http://lylejohnson.name/blog/2008/12/30/building-standalone-fxruby-applications-with-exerb/  I tried withrubyscript2exe but something went wrong and I just quickly tried exerb.  This worked although the exe files are huge at 9MB.

The key part is

1) creating the recipe file .exy

ruby -r exerb/mkexy hello2.rb

2) Converting the recipe file to an .exe

exerb hello2.rb

I ran into a problem with gruff as it seemed to have too many files.  I am trying to work out a solution.

Part of the block looked like this:

RMagick.rb:
  file: c:/ruby/lib/ruby/gems/1.8/gems/rmagick-2.9.0-x86-mswin32/lib/RMagick.rb
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/deprecated.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/base.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/area.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/bar_conversion.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/bar.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/line.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/pie.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/spider.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/net.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/stacked_mixin.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/stacked_area.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/stacked_bar.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/side_bar.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/side_stacked_bar.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/a_b.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/accumulator_bar.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/scene.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/mini/legend.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/mini/bar.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/mini/pie.rb:
c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/mini/side_bar.rb:
gruff.rb:
  file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff.rb

 

and I converted it to:

RMagick.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/rmagick-2.9.0-x86-mswin32/lib/RMagick.rb
./gruff/deprecated.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/deprecated.rb
./gruff/base.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/base.rb
./gruff/area.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/area.rb
./gruff/bar_conversion.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/bar_conversion.rb
./gruff/bar.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/bar.rb
./gruff/line.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/line.rb
./gruff/pie.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/pie.rb
./gruff/spider.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/spider.rb
./gruff/net.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/net.rb
./gruff/stacked_mixin.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/stacked_mixin.rb
./gruff/stacked_area.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/stacked_area.rb
./gruff/stacked_bar.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/stacked_bar.rb
./gruff/side_bar.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/side_bar.rb
./gruff/side_stacked_bar.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/side_stacked_bar.rb
./gruff/accumulator_bar.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/accumulator_bar.rb
./gruff/scene.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/scene.rb
./gruff/mini/legend.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/mini/legend.rb
./gruff/mini/bar.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/mini/bar.rb
./gruff/mini/pie.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/mini/pie.rb
./gruff/mini/side_bar.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff/mini/side_bar.rb
gruff.rb:
   file: c:/ruby/lib/ruby/gems/1.8/gems/gruff-0.3.4/lib/gruff.rb

 

It is still missing some DLL's that are needed so I will have to package those up separately.

A longer more detailed post is required on this.

2009-06-11

Language comparisons

I am developing a list of the variations between the languages I typically use:

Syntax

Feature Mathematica Ruby AutoITScript C# VBA Fame
End of line comment N/A # ;   '  
Inline comment (* *)          

Language grammar

Feature Mathematica Ruby AutoITScript C# VBA Fame
End of line comment   # ;   '  
Inline comment (* *)          
Exceptions Check[code,onfail] begin
rescue
end
    on error  

Functions

Feature Mathematica Ruby AutoITScript C# VBA Fame
Transpose Transpose[] a[0].zip *a[1..-1]

N/A

N/A N/A N/A
   

2009-06-10

Gruff and Rmagick on Windows

I read this post that thought it would be very easy and just what I need to produce some graphs.  However not quite so easy as I am on Windows.  I tried gem install rmagick-win32 but then I went here to get the rmagick instructions.

http://rmagick.rubyforge.org/install-faq.html

Which worked straightforwardly.   I had previously installed imagemagick on my system.  So now hopefully gem install gruff will work. Now had a problem with the rmagick expecting to be:

C:\ruby\lib\ruby\gems\1.8\gems\rmagick-2.9.0-x86-mswin32\ext

which was due to not rebooting my computer.

The documentation is here.  I have been trying to draw a side bar with +ve and +ve variations for our daily email:

advanced_graph

However I couldn't initially get the numbers to go negative. However with sources soon have knocked up:

advanced_graph

2009-06-08

Ruby and Nozbe

Tested out gettting data from Nozbe (an excellent list manager using Dave Allens GTD methodology for inspiration) for getting reports from projects:

Using the ruby-nozbe gem it was easy to get basic information (the user key comes from your account under Extras -> API:

require 'nozbe'

user_key ='cccetc etc'

projs = Nozbe::Project.list(user_key)

projs.each {|p| puts "Id:>#{p.id}< Name:>#{p.name} Count:>#{p.count}<<"}

However the whole thing fell rather flat as I had coded all my projects using Project Groups and I couldn't get the project groups out of Nozbe.

2009-06-05

First YAML log file

I started with an excellent 5 minute tutorial and then added some a simple log file to my code.  Now I have a more or less functional log file writer.  I will improve it as I go on.

# A class for creating logfiles that show what has been done
# designed to be able to fail half way through

class H3Log
  @@default_dir = "C:"
  @@default_prefix = "Logfile"
  @@file_name = ""
  @@indent = 0
  @@indent_text = ""

  def H3Log.timestamp
    return Time.now.strftime('%Y-%m-%dT%H:%M:%S')
  end

  def H3Log.set_default_dir(dir)
    @@default_dir = dir
  end

  def H3Log.set_prefix( prefix)
    @@default_prefix = prefix
  end

  def H3Log.start(msg)
    @@file_name = "#{@@default_dir}\\#{@@default_prefix} #{Time.now.strftime('%Y-%m-%dT%H%M%S')}.yaml" if @@file_name = ""
    @@index_text = ""
    (@@indent*2).times { @@index_text += " "}
    @@indent += 1
    H3Log.msg("--- " + msg)
    H3Log.msg("start: #{H3Log.timestamp}")
  end

  def H3Log.end()
    H3Log.msg("end: #{H3Log.timestamp}")
  end

  def H3Log.msg(msg)
    f=File.open(@@file_name, 'a' )
    f.write("#{@@index_text}#{msg}\n")
    f.close
  end

end

begin
  puts "Start"
  H3Log.set_default_dir('O:\mytestdir)
  H3Log.start("log start")
  H3Log.end
rescue
  puts "had to be rescued"
end

#puts "Waiting for readline #{Time.now} "
#readline

Calling external programs

Calling external programs is easy:

system('C:\Program Files\Me\live\ClickSomething.exe')

This is a blocking call so it doesn't return control until the enclosed exe file has run.

However if you want this to click on error messages while you are controlling an application then you need it to return immediately and then to kill the process later, you need something like this:

require 'win32/process'

f = IO.popen('C:\Program Files\Me\live\ClickSomething.exe')

#do work which might generate error dialogs
Process.kill(1,f.pid)

I had loaded up popen3 earlier but I was using that to capture text output.

2009-06-02

Copy of nice Date Time Format in Ruby

I got this from here http://snippets.dzone.com/posts/show/2255

------------------------------------------------------------------- Time#strftime
time.strftime( string ) => string
---------------------------------------------------------------------------------
Formats time according to the directives in the given format string. Any text not listed as a directive will be passed through to the output string.

"
Format meaning:

%a - The abbreviated weekday name (``Sun'')
%A - The full weekday name (``Sunday'')
%b - The abbreviated month name (``Jan'')
%B - The full month name (``January'')
%c - The preferred local date and time representation
%d - Day of the month (01..31)
%H - Hour of the day, 24-hour clock (00..23)
%I - Hour of the day, 12-hour clock (01..12)
%j - Day of the year (001..366)
%m - Month of the year (01..12)
%M - Minute of the hour (00..59)
%p - Meridian indicator (``AM'' or ``PM'')
%S - Second of the minute (00..60)
%U - Week number of the current year,
starting with the first Sunday as the first
day of the first week (00..53)
%W - Week number of the current year,
starting with the first Monday as the first
day of the first week (00..53)
%w - Day of the week (Sunday is 0, 0..6)
%x - Preferred representation for the date alone, no time
%X - Preferred representation for the time alone, no date
%y - Year without a century (00..99)
%Y - Year with century
%Z - Time zone name
%% - Literal ``%'' character

t = Time.now
t.strftime("Printed on %m/%d/%Y") #=> "Printed on 04/09/2003"
t.strftime("at %I:%M%p") #=> "at 08:56AM"
"

2009-06-01

Getting Excel addins to work with Ruby

I was having difficulty with this until I came across this post by bbiker.  The problem is that when you open Excel with a program OLE link it has a different environment to that when you open manually.  Specifically addins might not work and need to be kicked.

I put it to work with the following function in the test code (this is non working code as it requires your own addin name) :

require 'win32ole'

class UpdateAddInFile

  def addin_installed?(ai)
    begin
      puts "Addins: #{ai}? =  #{@xl.AddIns(ai).Installed}"
      @xl.AddIns(ai).Installed=0
      @xl.AddIns(ai).Installed=1
    rescue
      puts "<><>Testing for adding #{ai} caused an exception."
    end
  end

  def initialize(thisFile)
    @xl = WIN32OLE.new('Excel.Application')
    @xl.Visible = true
    addin_installed?("Your Add-In")
    #Satisfying wait as Excel addins get installed
    dummy = @xl.workbooks.Add # Add a new work book
    dummy.Cells(1,1).value = "add in functions will now work"
  end

end

UpdateAddInFile.new('c:\\test.xls')

puts "Waiting for readline"
readline

2009-05-28

Ruby log file management

I have been looking for a ruby log file management.  I looked at log4r but not sure it does what I want.  I am assuming a program that is run regularly to carry out a job. I would like the following:

1) for log files to be created for each run.  These to be stored in a log file directory

2) For the log files to be managed eg stored in log\2009\2009-04 and then a log file per day and a method of multiple log files per day.

3) The log files to have a format so that they are quite happy to crash in the middle, computer turned off and still be well formed and to have written everything that has been done out safely.  IE they can’t use regular XML format as can’t rely on closing brackets

4) Ideally have a graphical hierarchical tool to show you what happened.  Eg how long does something take over time, how often has it gone wrong.  A task would log start and end events. How many have happened per month etc  Looking at graphical environements this seems more suited to a rubyfx than shoes as rubyfx has support for a tree list.  Ideally the top levels of the tree would be the file directory structure.

5) The structure to cope with hierarchical logging eg

Start A
  Start B
  End B
  Start B
End A

Shows two B inside an A, on of the B’s was unsuccessful

6) Automatic log control eg ability to have only n Months of logs, to keep only say end of month logs after 1 year and even log thinnning where old logs are first thinned by getting rid of sub levels to take up less space.  Fixed space could also be used.

I think this is as far as I have got so far now back to some real work.

Later:  I have looked at YAML and think it is going to be helpful.

List of UK pottery suppliers

I have been trying to find a source of pottery supplies in the UK and wanted to list the main suppliers and some information about price.  I want to get some colour for the children to use, some clay, some slip for making bone china mugs in a mould and perhaps some plates.

I used ctm and they worked well.  Just need to get the kiln running now.

So in no particular order here they are:

Name Website Comments Price of colours Supplies clay,slip
http://www.ctmpotterssupplies.co.uk/ Complete supplier – good for larger quantities, based in Exeter. Delivery £10 per 25kg
Bit basic but functional web site
Scarva Erthenware glaze £5-£13 per kg powder, £6-£10 per 230ml/325g pot mixed.
Underglaze colour £2 to £4 per 100g pot. Pencils £3.20 each
porcelain 14.70 a gallon 8.64 for 12.5kg white earthenware
http://www.potterycrafts.co.uk/ Good website, Staffordshire
Duncan Covercoat
  Bone china and porcelain 5lt £21 £10 for 10KG for white earthenware
http://www.claymansupplies.co.uk/ Chichester    
http://www.dornhillpotterysupplies.co.uk/ Gloucestershire    
http://www.hobbyceramicraft.co.uk/ Focused on Ceramic cafe.    

2009-05-23

Playing with Ruby on rails on Windows

I have been playing with Rubymine which is a lovely development environment for Ruby.  I have a new web site development project so decided to learn Ruby on rails.

I have been reading Simply Rails 2 by Patrick Lenz and have now decided to actually try it out.  However the installation on Windows didn’t seem as straightforward as I thought it was going to be.  firstly the Instant rails seemed the wrong way to go as I already had ruby. So I did it by hand.

So I did gem install rails which worked fine.  Then downloaded rubymine and a 30 day free evaluation.  This then created my project for me.  So far so good.  I downloaded SQLite3 and put the .exe,.dll and .def in the Windows/ system32 directory, or rather I thought I did.   When I looked later Vista had prevented them.  So then I put them in c:\ruby\bin.

Had a bit of a problem with the SQL lite and I haven't been alone in this.  I created a data base with the SQL addin manager for Firefox.  Apart from using a .sqlite rather .sqlite3 that seemed ok. 

I had problems with installing the gem sqlite3-ruby and then I found this great post http://blog.emson.co.uk/2008/06/installing-sqlite3-on-windows-for-rails/

and it worked with gem install gem install sqlite3-ruby --version=1.2.3  and that seemed to work.

However ruby script/server still wouldn’t start up rails.  Time for supper.  The problem was I had corrupted the database.yaml file by adding a single character.  Once that was sorted it worked fine.

2009-04-30

Excel 2007 slow to open

Just a quick note I have both Excel 2003 and Excel 2007 and Excel 2007 was very slow to open.  A search of the web came up with the invaluable and crazy suggestion to create a blank .XLAM and add it.  Hey presto Excel 2007 responds as quickly as 2003.  Weird.

Starting a TortoiseSVN project

SVN is a great version control system and TortoiseSVN a great tool to access it.  I currently run the SVN server on a Windows desktop running as a serivce and each night back taking a rolling backup of it.

However I find one thing slightly confusing and that is how to move an existing project into SVN.  Typically you start something off and then decide it is worth a little more effort so add it to version control.  However the Tortoise SVN import tool is annoying in that when you import a folder it does not become the working copy.  So the help manual recommends you use Section 4.2.2, “Import in Place”.  This is then copied from the manual with some extra graphics:

  1. Use the repository browser to create a new project folder directly in the repository. (right click in File Explorer to get pp up menu then choose TortoiseSVN->Repo-Browserimage

  2. Checkout the new folder over the top of the folder you want to import. You will get a warning that the local folder is not empty. Now you have a versioned top level folder with unversioned content. (Make sure the URL of the repository contains the sub directory you have just added):image

  3. Use TortoiseSVN → Add... on this versioned folder to add some or all of the content. You can add and remove files, set svn:ignore properties on folders and make any other changes you need to.

  4. Commit the top level folder, and you have a new versioned tree, and a local working copy, created from your existing folder.

This is actually very easy but I remember getting confused before.

2009-02-13

Google logo for Darwin’s 200th birthday

I just liked it and was sad to be back to the regular logo so here it is:

image

2009-02-02

Wiring my phone system over Ethernet cabling

In our house i have run a number of Ethernet cables around for future expansion and for easy access.  However I had not quite finished all the wiring.  Anyway I need to move the phone (UK) from one room to another and was going to do it via the Ethernet cabling.  However I have forgotten the wiring for the phone.

So I have just summarised here the various connections:

Ethernet comes in two standard wiring patterns T568A (US govt standard and T568B AT&T standard).  I was going to choose the A version until I check some of my patch cables which were T-568B.  See incentre links:

image

From Zytrax I got an example wiring pattern for telephone links:

Pin number   Strand colour Name
1 Tip white & orange LAN (TX_D1+)
2 Ring orange LAN (TX_D1-)
3 Tip white & green LAN (RX_D2+)
4 Ring blue Telephone 1
5 Tip white & blue Telephone 1
6 Ring green LAN (RX_D2-)
7 Tip white & brown Telephone 2
8 Ring brown Telephone 2

(Tip and Ring comes from TRS Tip Ring Sleeve eg old telephone jacks) and I just need some standard BT wiring pattern for the UK standard 6 pin connector with 5 and 2 the normal for the wiring (but not master extension):

image

2009-01-30

Excel madness

My Excel instance has been corrupted by problems with my roaming profile.  So now on running a macro when it comes across a Chr function I get the error Compile error: Can’t find project or library.

image

You just need to unclick the missing reference

image

Then all will be well.

See also

http://support.microsoft.com/kb/q208218/

2009-01-20

Draft note about automating Excel scripts

I run a number of Excel macros which rely on addins eg Fame to work. In order to make sure these happen reliably I run these in the following manner.

On a PC which is the controller I run daily tasks. The list of daily tasks has a graphical user IF that runs the tasks once. It also has an automatic mode which is run from the scheduler.

Eg DailyTasks.au3 –> DailyTasks.exe graphical editor

Then the actually running of the tasks is done by

DoDailyTasks.au3 –>DoDailyTasks.exe

These are compiled to Exes to make the whole thing more reliable and less dependent on a number of files being in the right version and in the right place.

To run an excel macro I have a script that opens a Remote Desktop connection and then runs a child script. This child script may need to be placed in the Program Files subdirectory of the Remote Desktop computer in order to have permission to run automatically.

The child script will open and Excel file and run a specific Macro.

The macro closes Excel when finished. The child script is waiting for excel to close and then closes the remote desktop. This then allows the top level script to continue to the next action.

The Excel file that it runs is usually just a pretty blank file and is a receptacle for VB code to run other Excel sheets. I have enclosed some sample code:

Sub aa_UpdateSomething()
GetConstants
aa_StartUpTrick
Dim i
SeriesRow = 2
While st(SeriesRow, 1) <> ""
UpdateSomething

SeriesRow = SeriesRow + 1
Wend
GetConstantsClose
End Sub

Sub bb_UpdateSomething()
aa_UpdateSomething
Application.Quit
End Sub

Private Sub UpdateSomething

End Sub

The aa_ version is designed to run by hand from the spreadsheet for testing and the bb_ version if designed to be run automatically.

Remembering to update include settings for AutoIT when upgrading versions

When I updated AutoIT it wiped my include path settings. So here is the script I used to show and update them (I used the great Koda gui builder tool):

#include <GUIConstants.au3>

#Region ### START Koda GUI section ### Form=O:\Automation\Include\AutoitIncludeSettings.kxf
$Form2 = GUICreate("Dialog", 329, 238, 347, 263)
GUISetIcon("D:\003.ico")
$GroupBox1 = GUICtrlCreateGroup("", 8, 1, 297, 193)
$Label1 = GUICtrlCreateLabel("Registry key for AutoIt searching for include files. (seperate with ;)", 24, 20, 230, 30)

$Edit1 = GUICtrlCreateEdit(RegRead( "HKEY_CURRENT_USER\Software\AutoIt v3\AutoIt","Include"), 16, 48, 281, 137)
;GUICtrlSetData(-1, "AEdit1")

GUICtrlCreateGroup("", -99, -99, 1, 1)
$Button1 = GUICtrlCreateButton("&OK", 65, 203, 75, 25, 0)
$Button2 = GUICtrlCreateButton("&Cancel", 162, 203, 75, 25, 0)
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
$DoAction = False
Exit
Case $Button1
$DoAction = True
ExitLoop
Case $Button2
$DoAction = False
ExitLoop
EndSwitch
WEnd

If $DoAction Then
RegWrite("HKEY_CURRENT_USER\Software\AutoIt v3\AutoIt","Include", "REG_SZ", GUICtrlRead($Edit1))
EndIf

2009-01-19

Getting Python to work

I have been investigating Python as I thought it might have a slightly simpler syntax from Ruby (I am finding it takes time to switch syntax from Mathematica, C#, VB etc) , be more better supported on .NET eg IronPython ahead of IronRuby and better scientific support and some work done on creating an interactive platform like Mathematica/Matlab. Enthought has a prepackaged Scientific distribution.

Anyway just to say not quite as good as I hoped in that Python is the middle of a big change in syntax from 2.x to 3.x, IronPython wont install on my server and it seems to work slightly differently on different computers when driving Excel.

After playing and buying a number of tools the free ActiveState Python distribution seems to work ok. The only snag I have found so far is that the PythonWin debugger really needs to be killed and restarted to work well if you are debugging.

2009-01-08

Great installation tool

Just wanted to mark down a great down installation tool, Inno Setup that I have used for years and written by Jordan Russell in Delphi.

http://www.innosetup.com/isinfo.php

2009-01-07

Moving Outlook messages with one click

This web post has great instructions (However last time I clicked it had disappeared (I wanted the macro instructions) so I have pasted it on the bottom)

http://www.fiftyfoureleven.com/weblog/general/outlook-email-shortcuts

but the first link was broken:

http://verychewy.com/archive/2006/04/12/outlook-macro-to-move-an-email-to-folder.aspx

 

Outlook Keyboard Shortcuts to Move a Message to a Folder

Published in General on Tuesday, June 6th, 2006

Ever wished that you could click alt+1 on your keyboard and have a hilighted message sorted into the correct folder in Outlook? Me too. I've been looking for an easy solution for this for a while but never really dug in to figure it out. Now I have the answer!

Inbox Zero

So I've been following Merlin Mann's Inbox Zero series from the fringes, interested in the concepts and envious of the many mac only solutions that he provides.

I've wanted a one click solution for organizing my inbox for some time, and looked but not found the answer. Well, spurred on by my new blog (plug), and the fact that I am waaay behind on a lot of e-mail (sorry to those still waiting for a reply) I've found a solution.

Three steps to two button Outlook e-mail sorting

What I am talking about here is having the ability to use a keyboard shortcut that shunts a selected/hilighted message in your Outlook inbox into a determined folder. In this manner, you can quickly sort a busy inbox into action/response/received messages, for example.

Note that I am using Microsoft Outlook 2003 on a Windows XP setup to do this. Your mileage may vary.

The process involves three relatively simple steps:

  1. Create a macro to move the e-mail to a specified folder.
  2. Create a digital certificate for the macro.
  3. Create a toolbar and keyboard shortcut to fire the macro with ease.

So, lets look at these in details...

1. Create a macro to move the e-mail to a specified folder

A little bit of googling brought me to Outlook Macro to Move an Email to Folder. This really made things easy.

You are going to want to take the code from that example and use the instructions found at Create a macro.

Keep in mind that you need to replace _Reviewed in the following line of code with the folder where you are moving your mail to: Set objFolder = objInbox.Folders("_Reviewed"). Also, the target folder has to be in the inbox.

2. Create a digital certificate for the macro

Once you've made your macro, Outlook (or winXP) will block the macro from running if your security settings are higher than low, which most people's are. To get around this you need to add a digital signature to the macro.

Fine, so where do you get the digital signature file?

I found the answer at OFF2000: Using SelfCert to Create a Digital Certificate for VBA Projects. Follow those instructions (it's quite simple) and then come back here for the next part.

Next we need to add the signature to the macro. Also quite easy, following the instructions at Add a digital signature to a macro project.

3. Create a toolbar and keyboard shortcut to fire the macro with ease

Here I refer you to the Outlook 2003 tip o' the day which teaches one how to add a new button to the toolbar and give it a keyboard shortcut. For our purposes, on step 3 of that article you will want to choose macros from the left hand pane and then the macro you wrote from step 1 from the right hand pane.

And that's it!!

In the end it was quite simple. I have no idea why it took me so long to find the answers to doing this (this has been on the wish list for awhile). I suspect I had some luck somewhere along the line this morning with some fortuitous googling finding me the right answers :).