Jacek Kotowski's toolbox.

piątek, 4 grudnia 2015

Stay secure for free

Talk & instant messages: https://whispersystems.org/
Disc: https://veracrypt.codeplex.com/
Files and mail: http://www.gpg4usb.org/

środa, 2 grudnia 2015

Coffee break - Access transform-pivot scavenging.

Looking for articles on Transform - Pivot, ie. crosstab queries in Access for my VBA ACE SQL solutions. I need it because my Excel files need to work with 100k+ data, and a lot of filtering, joining etc. Interesting: http://blogannath.blogspot.com/2010/02/microsoft-access-tips-tricks-crosstab.html#ixzz3t3Pb0e6j And more. http://datapigtechnologies.com/blog/index.php/running-crosstab-queries-in-excel/ http://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/ http://allenbrowne.com/ser-67.html http://www.mrexcel.com/forum/microsoft-access/261348-help-transform-pivot-crosstab-querys-access.html http://www.fmsinc.com/MicrosoftAccess/query/crosstab-report/

Coffee Break ListobjectTables Totals in VBA

I found good tutorial on ListObject Tables in VBA here:
http://www.thespreadsheetguru.com/blog/2014/6/20/the-vba-guide-to-listobject-excel-tables

I wanted to insert totals in selected columns, my playground looked like this:

Function to see total of selected column:

Function sumColumnCenaNetto()
    sumColumnCenaNetto = WorksheetFunction.sum _
    (Sheets("Sheet1").ListObjects("Table1").ListColumns("CenaNetto").DataBodyRange)
End Function

Insert a total formula under the table.

Sub InsertTotal()
   With Sheets("Sheet1").ListObjects("Table1")
      .ListColumns("CenaNetto").TotalsCalculation = 1
      .ShowTotals = True
End Sub

poniedziałek, 16 listopada 2015

Coffee breaks and Braindumps Data Mining

Explore Data Mining Open Source
Open Source data mining to explore:
Orange from University of Liubliana http://orange.biolab.si/
KNIME https://www.knime.org/downloads/knime/win64exe
DataMelt http://jwork.org/dmelt/
SciKit Learn http://scikit-learn.org/stable/#

For Excel OpenSolver http://opensolver.org/
Alg lib: http://mycomputerlessons.blogspot.com/2011/01/tutorial-numerical-analysis-in-excel.html
and https://newtonexcelbach.wordpress.com/2012/09/30/daily-download-13-the-alglib-maths-library-and-excel/

HOXO-M - anonymous data analyst group in Japan -
http://mockquant.blogspot.com/

https://www.coursera.org/learn/machine-learning

Saving (Multidimensional) Arrays from VBA to disk and retrieving it back
Old topic: moving data arrays and other data structures betw. vba and excel
https://newtonexcelbach.wordpress.com/2012/10/17/daily-download-30-data-transfer-to-and-from-vba/
http://www.wiseowl.co.uk/blog/s224/multi-dimensional-array.htm
http://stackoverflow.com/questions/18114527/load-multidimensional-vba-array-from-disk
http://www.xtremevbtalk.com/archive/index.php/t-259568.html

put/get:

Open X_FILE For Binary As #1

Put #1,,X

Close #1


To recall x from file:

Get #1,,X

http://www.analystcave.com/excel-vba-dictionary-arrays-and-other-data-structures/
(and nice cheatsheat http://www.analystcave.com/excel-vba-tutorial/#Loops
http://www.analystcave.com/excel-regex-tutorial/)

Miscellaneous
Excel VBA (nice plugins for parallel computing in Excel, saving data from VBA, optimising):
http://www.analystcave.com/excel-saving-your-vba-data/

wtorek, 10 listopada 2015

Google Tensor Flow Library available for all.

Interesting: http://www.dobreprogramy.pl/Maszynowe-uczenie-sie-teraz-dla-kazdego.-Google-otworzylo-silnik-AI-TensorFlow,News,68071.html

Getting started: http://tensorflow.org/get_started/os_setup.md
Reading digits: http://colah.github.io/posts/2014-10-Visualizing-MNIST/