15 Evening study update

I made a decision to dedicate the evening session to my AFL code which I felt was more urgent.. I feel I have some momentum there that I need to capitalise on. So I did some extra work on the traderkarl record.

A separate entry exists there to reflect the extra work in addition to today’s hometrip.

I will discuss with (J) whether the evening VBA session can be converted into a AB AFL session until the Amibroker template is done.

Apart from that I pulled the stats book out and sifted through more of the excel examples in Chapter 2. It seems my efforts here are a bit diluted.
Chapter2 week 4 of 3.

13/10/15

October 13, 2015UncategorizedTraderKarl

Chapter2 week4 of 3

Used my evening session to begin a compendium of useful excel formulae and a repository of useful macro code. Some of the macro code will need to be manually input from printouts.

Continuing with sorting through what is useful in ch2. of stats. I think I pretty much know the excel content for this chapter. It gets quite specific about methodology. Not sure I need that.

12/10/15 VBA and stats evening study

October 12, 2015UncategorizedTraderKarl

I spent a significant part of the day in spreadsheets sorting through data and sifting out stats and patterns.
Some new stuff fell out by way of learning.

Countif – an if statement that checks if something is true and then counts the number of times it has happened in a range
Countifs – same as the above, but checks a number of ranges for a number of things and returns the count for when all the conditions are true.

Sadly, the countif only works for NUMBERS not strings.
Funnily enough, there is no CountAif or CountAifs. wtf.

So that means I had to find a way to do it manually.

Luckily a google search has found someone who has already solved this!
The way to do this is to use the curly brackets with a sum(if..) statements for the ranges.
Using ctrl+shift+enter to set the formula up for an array.

https://support.microsoft.com/en-us/kb/275166

8/10/15 Thursday evening study VBA

October 8, 2015UncategorizedTraderKarl

I spent some of my evening researching the Vlookup function as I had to match an array of data to individual cell entries.

The best way to get it done, in my view, was not to use vlookup, but instead to use a combination of match and index functions.

=VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup))

Lookup_value – The value you want to find in the lookup value column
Lookup_value – The table range containing columns for both the lookup and return values
Col_index_num – The index number for the column containing the return values
Range_lookup – The type of match: Nearest Less Than (TRUE), or Exact (FALSE) [optional]

INDEX returns the value at the intersection of a row and column in a given range.

Formula: =INDEX(Array, Row_num, Column_num)
- Array – The range of cells
- Row_num – The row to return data from
- Column_num – The column to return the data from [optional]

MATCH returns a position of an item in an array that matches a value.
- Formula: =MATCH(Lookup_value, Lookup_array, Match_type)
  - Lookup_value – The value you want to find in the lookup value array
  - Lookup_array – The range containing lookup values
  - Match_type – Exact (0), Nearest Less Than (-1), or Nearest Greater Than (1) [optional]

There are plenty of internet resources to help learn this that also discuss limitations of the vlookup function:
http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/
http://fiveminutelessons.com/learn-microsoft-excel/how-use-index-match-instead-vlookup?PageSpeed=noscript
http://thinketg.com/say-goodbye-to-vlookup-and-hello-to-index-match/
http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/

The chapter 2 stats book excel examples use vlookup a lot.
I think a worthwhile activity next week will be to look through the examples and see if I can use index match to solve them.

As I am behind in my reading of chapter2, I commit to finishing reading the chapter this weekend and then start on the excel examples on Monday.