Day 2 with R. Ideas for diving in deep waters

Start with example here: https://stat.ethz.ch/R-manual/R-devel/library/class/html/knn.html
Then go through: http://blog.datacamp.com/machine-learning-in-r/
========================
Additional reading:
https://en.wikibooks.org/wiki/Data_Mining_Algorithms_In_R/Classification/kNN
https://www3.nd.edu/~steve/computing_with_data/17_Refining_kNN/refining_knn.html
http://blog.webagesolutions.com/archives/1164
http://www.analyticsvidhya.com/blog/2015/08/learning-concept-knn-algorithms-programming/

K means vs K nearest neighbors are two different things.
https://www.quora.com/Is-the-k-Means-algorithm-related-to-the-k-Nearest-Neighbors-algorithm
https://www.quora.com/How-is-the-k-nearest-neighbor-algorithm-different-from-k-means-clustering

What next:
http://rayli.net/blog/data/top-10-data-mining-algorithms-in-plain-r/

Additional topic:
R and SQL/MS SQL
http://www.burns-stat.com/translating-r-sql-basics/
http://www.r-bloggers.com/make-r-speak-sql-with-sqldf/ - use SQL in R
https://www.simple-talk.com/sql/reporting-services/making-data-analytics-simpler-sql-server-and-r/  make R talk to MS SQL

Ideas for day 3: http://courses.had.co.nz/10-tokyo/
http://www.analyticsvidhya.com/learning-paths-data-science-business-analytics-business-intelligence-big-data/learning-path-r-data-science/

Picturen Lite: best app for viewing lectures on android.

This app appears to be the best choice for viewing video lectures (esp. downloaded khanacademy lessons, R coding lectures) on small devices. One can zoom in to the code being written by the lecturer with fingers. Great. Big thanks to the authors. One fault though (bug? please correct) After pausing and ocking/unlocking the screen it does not remember the last position and starts playing the video from the beginning.

If you know a better one, please let me know.

Free books from Grean Tea Press

Think Stats, Think Python, Think Bayes. Visit: http://greenteapress.com/wp/

Day 1 with R

What I have learned today:
> setwd("C:\R")  - set the directory I will put my files,
> getwd() check if it is set correctly

Let's get some data ...

I created a csv file with notepad:

Nazwisko,Wartosc,Wartosc2
Kowalski,23.22,32.12
Nowak,21.21,34.12
Sikorowski,20.2,11.3

Then I read it with R

> b <- read.csv ("test.csv") 

Play with charts:

> plot(b$Wartosc, b$Wartosc2)

> plot(density(b$Wartosc)) - basic density plot

> plot(boxplot(b$Wartosc)) - basic boxplot


Play with k-means clusters
clusters <- kmeans(b[-1],2)

Get data with cluster no. column
 > b <- cbind (b, clusters$cluster)

Plot x y with clusters distinguished by color:
> plot (b$Wartosc, b$Wartosc2, col=b[,3])

To add labels I tried:
> text(b$Wartosc, b$Wartosc2, labels=b$Nazwisko, cex= 0.7, pos=3)

Then I played with http://stats.stackexchange.com/questions/109273/creating-a-cluster-analysis-on-multiple-variables

> numbers_only <- b[c(-1,-4)]
> rownames(numbers_only) <- b$Nazwisko
> d <- dist(numbers_only, method="euclidean")
> fit <- hclust(d, method = "ward.D")
> plot(fit)

What if the tree is too large, and I want to generalize clusters. I need to cut a tree? http://stackoverflow.com/questions/6518133/clustering-list-for-hclust-function

> cutree(fit, h=10)
  Kowalski      Nowak Sikorowski
         1          1          2

Shit, it is 01:00 AM. Good night

Code for Poland

http://kodujdlapolski.pl/projekty/

Plugin for easy swapping data between R and Excel

http://sharpstatistics.co.uk/excel-r-data/

Is there a Python-Studio like R-Studio?

I visited R-Bloggers and they redirected me today to http://blog.yhathq.com/posts/rodeo-native.html
need to check it out.

Publication on closed gov system in Poland

http://www.isppan.waw.pl/ksiegarnia/system_zamkniety.htm

Politico: Sober look at Polish politics

http://www.politico.eu/article/give-pis-a-chance-poland-kaczynski-andrzej-duda-szydlo/

CoffeeBreak: Outliers2, what about skewness

My new version of outlier's highlighting VBA

Sub outliers_IQR()

Dim Rng As Range, rTest As Range: Set rTest = Selection
Dim rQ1 As Double: rQ1 = WorksheetFunction.Quartile(rTest, 1)
Dim rQ3 As Double: rQ3 = WorksheetFunction.Quartile(rTest, 3)
Dim IQR As Double: IQR = Q3 - Q1
Dim Factor As Double: Factor = 2.2


For Each Rng In rTest
    If Rng > (rQ3 + Factor * IQR) Or Rng < (rQ1 - Factor * IQR) Then
        Rng.Interior.Color = RGB(255, 0, 0)        '.Value = "Outlier" 'or delete the data with Rng.clearcontents
    Else
        Rng.Interior.Color = xlNone
    End If
Next

End Sub


What about skewness? The proposal to deal with it can be found here: https://wis.kuleuven.be/stat/robust/papers/2008/outlierdetectionskeweddata-revision.pdf
I need to decipher this math and implement it above.


Some related reading:
http://www.real-statistics.com/students-t-distribution/identifying-outliers-using-t-distribution/grubbs-test/
http://datapigtechnologies.com/blog/index.php/highlighting-outliers-in-your-data-with-the-tukey-method/
http://stats.stackexchange.com/questions/60235/how-accurate-is-iqr-for-detecting-outliers

Excel mark duplicates.

Simple and interesting:
- in one helper column concatenate the columns you are checking for duplicates.
- in another =COUNTIF([concatenated_colum_range];checked_concatenated_value)>1
It returns TRUE when there are more than 1.

Found here: http://www.ozgrid.com/forum/showthread.php?t=59302 and http://www.ozgrid.com/Excel/highlight-duplicates.htm and http://blog.contextures.com/archives/2013/04/11/highlight-duplicate-records-in-an-excel-list/

CoffeeBreak: Outliers, Kernel Density

Reading on using IQR to identify outliers in Excel:
http://datapigtechnologies.com/blog/index.php/highlighting-outliers-in-your-data-with-the-tukey-method/
http://brownmath.com/stat/nchkxl.htm
esp. see worksheet: http://brownmath.com/stat/prog/normalitycheck.xlsm

Using SD, not a good idea but a good macro to start with:
http://www.mrexcel.com/forum/excel-questions/732424-how-remove-outliers-data-set-2.html

Sub outliers_mod2()
Dim dblAverage As Double, dblStdDev As Double
Dim NoStdDevs As Integer
Dim rTest As Range, Rng As Range
'Application.ScreenUpdating = False
NoStdDevs = 3 'adjust to your outlier preference of sigma
Set rTest = Selection 'Application.InputBox("Select a range", "Get Range", Type:=8)
dblAverage = WorksheetFunction.Average(rTest)
dblStdDev = WorksheetFunction.StDev(rTest)
For Each Rng In rTest
    If Rng > dblAverage + NoStdDevs * dblStdDev Or Rng < dblAverage - NoStdDevs * dblStdDev Then
        Rng.Interior.Color = RGB(255, 0, 0)        '.Value = "Outlier" 'or delete the data with Rng.clearcontents
    End If
Next
'Application.ScreenUpdating = True
End Sub 

====

Normalize(simple linear normalize) data in Excel

http://stats.stackexchange.com/questions/70801/how-to-normalize-data-to-0-1-range?newreg=f530ce192d144b109ec26a077cab00af 

Kernel Density/Regression, alternatives to histogram.

==== 

good article: http://www.stat-d.si/mz/mz4.1/vidmar.pdf
http://people.revoledu.com/kardi/tutorial/index.html

2d: http://www.r-bloggers.com/recipe-for-computing-and-sampling-multivariate-kernel-density-estimates-and-plotting-contours-for-2d-kdes/

Density plugin: http://www.prodomosua.eu/ppage02.html 

Good  VBA example for density plots (incl UDF function). 

http://www.iimahd.ernet.in/~jrvarma/software.php

(found in this page: http://www.mathfinance.cn/category/vba/1/5/)

Some R code http://www.wessa.net/rwasp_density.wasp#output

Another article: http://www.rsc.org/images/data-distributions-kernel-density-technical-brief-4_tcm18-214836.pdf

Some plugin/vba to check: http://www.rsc.org/Membership/Networking/InterestGroups/Analytical/AMC/Software/RobustStatistics.asp

Cheatsheets for R: distributions: http://www.r-bloggers.com/ggplot2-cheatsheet-for-visualizing-distributions/

Good Wikipedia entry: https://en.wikipedia.org/wiki/Outlier
Advanced article: http://d-scholarship.pitt.edu/7948/1/Seo.pdf
For beginners: https://www.dataz.io/display/Public/2013/03/20/Describing+Data%3A+Why+median+and+IQR+are+often+better+than+mean+and+standard+deviation

====
Simple MAD solution with Excel formulas: http://www.codeproject.com/Tips/214330/Statistical-Outliers-detection

Coffee break: MSAccess Transform/Pivot syntax.

http://blogannath.blogspot.com/2010/02/microsoft-access-tips-tricks-crosstab.html
http://stackoverflow.com/questions/27710313/ms-access-sql-transform-aggregate-manipluation-of-values-for-pivot

pivot datepart (aby otrzymać quarters)
https://msdn.microsoft.com/en-us/library/bb208956%28v=office.12%29.aspx
http://weblogs.sqlteam.com/jeffs/archive/2007/09/10/group-by-month-sql.aspx

sum if
http://allenbrowne.com/ser-67.html  
http://www.blueclaw-db.com/accessquerysql/crosstab.htm

Crosstabs on two columns
http://stackoverflow.com/questions/24706235/sql-pivot-cross-tab-and-multiple-columns

Stay secure for free

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

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

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/

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/

Bus reading on trends in R

http://www.r-bloggers.com/assessing-the-forecasting-ability-of-our-model/

MARS vs linear regression...

https://en.m.wikipedia.org/wiki/Multivariate_adaptive_regression_splines

Excel clustering and other interesting plugins

Have a look at these plugins, especially "cluster"http://www.prodomosua.eu/ppage02.html

Clustering 4: K-means algorithm

Excel Cluster Analysis Example

Excel: Iterate through x, return y's to table

My own humble attempts in VBA:

Enter one x and record 3 outputs:

Sub simulate()
    Dim x, xStart, xEnd, xStep As Single
    Dim i  As Integer


 With Worksheets("Simulate")
     
     i = 1
     xStart = .Range("f4").Value
     xEnd = .Range("g4").Value
     xStep = .Range("h4").Value
    
    
    .ListObjects("tblSim").DataBodyRange.Delete 'Shift:=xlToUp
    
    
    For x = xStart To xEnd Step xStep
        Worksheets("ProductList").Range("D10").Value = x
        With .Range("a1")
        .Offset(i, 0).Value = x
        .Offset(i, 1).Value = Worksheets("Analysis").Range("N19").Value
        .Offset(i, 2).Value = Worksheets("Analysis").Range("N20").Value
        .Offset(i, 3).Value = Worksheets("Analysis").Range("N21").Value
        End With
        i = i + 1
    Next x
    
End With
    
End Sub

Enter two variables and record one output, in a table suitable for a 3d plot (eg. heat map).

Sub simulate()
    Dim x, xStart, xEnd, xStep as Double
    Dim i, j  As Integer
    Dim y, yStart, yEnd, yStep as Double

On Error Resume Next
  
 With Worksheets("Simulate")
 
'Get iteration parameters

     xStart = .Range("h4").Value
     xEnd = .Range("i4").Value
     xStep = .Range("j4").Value
     
     yStart = .Range("h5").Value
     yEnd = .Range("i5").Value
     yStep = .Range("j5").Value
     
   'Clear old data
       '.ListObjects("tblSim").DataBodyRange.Delete 'Shift:=xlToUp
    .Range("A10:L100").ClearContents
     
'Generate x axis
    
    i = 1
    
    For x = xStart To xEnd Step xStep
            .Range("a20").Offset(0, i).Value = x
            i = i + 1
    Next x
    
'Get results
   
    j = 1
    For y = yStart To yEnd Step yStep
        .Range("a20").Offset(j, 0).Value = y
     
        i = 1
        
        Worksheets("ProductList").Range("D11").Value = y
        
        For x = xStart To xEnd Step xStep
            Worksheets("ProductList").Range("D10").Value = x
            .Range("a20").Offset(j, i).Value = Worksheets("ProductList").Range("N21").Value
            i = i + 1
        Next x
        
     j = j + 1
        
     Next y
     
  End With
    
End Sub

What about more dimensions? I think it would be the best occasion to feed an array in vba (then... how to save it?) or feed a cube in MS SQL. About saving/loading an array: http://stackoverflow.com/questions/18114527/load-multidimensional-vba-array-from-disk http://www.vb-helper.com/howto_read_write_binary_file.html

Telegraph on Elections in Poland

http://www.telegraph.co.uk/news/worldnews/europe/poland/11954733/Who-are-Polands-victorious-Law-and-Justice-party-and-what-do-they-want.html

KNBIT and Polish Machine Learning Course

http://knbit.edu.pl/pl/wiki/ai/kurs-machine-learning/

Late night photography readings: superresolution

Stacking for superresolution:
Very simple, interesting and promising technology:
http://petapixel.com/2015/02/21/a-practical-guide-to-creating-superresolution-photos-with-photoshop/
https://en.wikipedia.org/wiki/Superresolution
http://xcorr.net/2011/09/15/video-super-resolution-coming-to-consumer-software/
http://www.gizmag.com/super-resolution-weizmann-institute/23486/

Free Stanford material - Intro To Statistical Learning

Read on LinkedIn group: https://www.linkedin.com/pulse/stanford-open-course-statistical-learning-very-good-who-lin-sun
and R-Bloggers http://www.r-bloggers.com/in-depth-introduction-to-machine-learning-in-15-hours-of-expert-videos/

Visited: http://www-bcf.usc.edu/~gareth/ISL/ and helped myself with the handbook. See also: http://statweb.stanford.edu/~tibs/ElemStatLearn/

Signed up and accessed the Course at Stanford: https://lagunita.stanford.edu/courses/HumanitiesScience/StatLearning/Winter2014/courseware/f6eb0a2902904c6e8f74a2c15833d1ad/dbbe6095fa55431b8253590e2658dc3e/

Collection of datasets: https://cran.r-project.org/web/packages/ISLR/index.html

Another course based on the book: http://www.alsharif.info/#!iom530/c21o7 

Thank you Lin Sun for this great info!

OT. Also free. Old versions of Rapid Miner. Somehow it is difficult for me to localize and download  the old free versions at authors' site: http://www.win.tue.nl/~rmans/RapidMiner/doku.php?id=wiki:installation

Excel vba: Best macro to remove empty rows from listobject tables.

Best reliable, highly recommended: 'http://www.excelforum.com/excel-programming-vba-macros/847446-auto-resize-shrink-table-on-an-active-sheet.html

Sub ert()
Dim lo As ListObject
'Application.ScreenUpdating = False
On Error Resume Next    'if SpecialCells(4) not exists
For Each lo In ActiveSheet.ListObjects
    With lo
        .ListColumns(1).Range.SpecialCells(4).Resize(, .ListColumns.Count).Delete shift:=xlUp
    End With
Next lo
'Application.ScreenUpdating = True
End Sub

trx toys for my birthday?

http://www.youkits.com/

Leanpub bookshop: free books - R, statistics, data mining!

I highly recommend https://leanpub.com/

• If you are poor, most books are free of charge. Initial price is a suggestion.
• If you wish to contribute, move the slider to select the contribution. See how much author earns!
• Buy the book when it is developed - author gets encouragement and early income!
• Some books accompany free online courses, eg. coursera.
• Select epub, mobi, pdf....

I selected a book R Programming for Data Science by Roger D. Peng

PS/OT This reminded me about a wonderful book about programming and C# fundamentals: http://www.introprogramming.info/english-intro-csharp-book/ 

Learn R, learn C#, it costs nothing!

Where to learn R... index 2015

Learning R: Index of Online R Courses, October 2015: http://blog.revolutionanalytics.com/beginner-tips/

Sunday Coffee: workflow editors in R like MS Data Tools

I am looking at various data workflow plugins and addins for R that would be equivalent to superb Microsoft Data Tools. (https://msdn.microsoft.com/en-us/library/mt204009.aspx My complaint: these tools are superb as experienced during job training, but not yet working in my MS SQL 2014 Developer at home). That is why I am looking for alternatives and so far I have identified the following:

1. Red-R
http://decisionstats.com/2010/04/20/using-red-r-r-with-a-visual-interface/

2. EF-Prime
http://www.ef-prime.com/products/ranalyticflow_en/quicktour.html

Data mining with VBA - interesting tools.

Have a look at these three tools :-) https://www.sites.google.com/site/sayhello2angshu/dminexcel

Disapponted with Microsoft, lost money, time and stomach ache...(Data Mining, SSAS, Data Tools )

Short version:
MS SQL 2014 Developer's Version plus Data Tools (SSAS, SSIS) plus Excel Data Mining Plugin equals lots of hopes and big disappointment involving loss of valuable time and money. 

=========

These will be mild words after some anger reduction meditation.

I was preparing my laptop for Business Intel and Data Mining training. I had  already lost one night  for installing recently purchased MS SQL 2014 Developers Version over unknown issues and error messages  giving no clue. Then I found on some blog there are two or three files left over by earlier version of MS SQL, innocent setup files left over after an uninstall, preventing the new version from installing. I hoped it was over.

I intended to use my computer instead of the virtual environment offered during the BI course, so that I could bring home and experiment with the examples. It appeared that neither Visual Studio Data Tools for SSAS and SSIS, nor the Excel Data Mining plugin will work.
I tried another night to resolve the issue, reading posts, with no luck. I discovered I am not alone with questions and problems that noone could successfuly address. I also studied professional forums only to learn that now I should install Data Tools separately and try Excel plugin for data mining version ver. 2012 SP 2 with no guarantee for a success.
The instructor of the Data Mining course tried to assist me but after a couple of minutes gave up and proposed I should rather work on a virtual machine. I tried to update Windows with all the current patches, uninstall and then  reinstall Visual Studio and MS SQL Server. With no luck

The software is now  installed but it is not functional as expected. The Excel data mining plugin will not work and the Data Tools are not present in MS Visual Studio.

I also tried to contact MS Tech Support, describing the problem. I went through automatic machines to reach a human. He requested I should run a scan for integrity of the system. There were no errors. He offered to call next day which I did, I prepared a description of my problem and requested an email where I can send it... but I was not given the option on the next day. I also tried to fill in the form and describe the issue in as much detail as possible (environment, software versions, error messages). I pasted it in to learn there was a length limit for text and my message was cut after several sentences. I was really pissed off with Microsoft.

I am loosing my patience. I have alreday purchased a product that is not functional... It does not do what it promises, what was expected.  Gosh! 

PS: More reading:
Google:
data mining add in for microsoft excel
"object reference not set to an instance of an object"

https://social.msdn.microsoft.com/Forums/en-US/8d1d3c3b-be6a-4a3e-9fc0-7f97be291432/data-mining-excel-2013-error-object-reference-not-set-to-an-instance-of-an-object?forum=sqldatamining

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/3abbf7d2-5b3a-47ab-b742-bb38835f0492/error-with-excel-data-mining-and-windows-81?forum=sqldatamining

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f6e52e58-6a8a-4b96-8f2d-e1278777a825/excel-data-mining-addin-error-object-not-set-to-an-instance-of-object?forum=sqldatamining

https://social.msdn.microsoft.com/Forums/windowsdesktop/en-US/86a42e38-051a-4d85-bb73-601b35492eb6/object-reference-not-set-to-an-instance-of-an-object?forum=sqldatamining


https://social.msdn.microsoft.com/Forums/sqlserver/en-US/65d11506-b005-44ba-b9e5-4d7053374505/sql-server-data-tools-for-visual-studio-2013?forum=ssdt

http://blog.nwcadence.com/sql-server-data-tools-clearing-up-the-confusion/

And so on... a lot of time and nerves lost by others. 

PS 2... a letter to Microsoft (but no address to send it - in Polish):

Szanowni Państwo,

W związku z moim uczestnictwem w kursie dotyczącym Data Mining w środiwisku MS SQL chcialem skorzystać z możliwości nauki własnej. Zakupiłem więc Excel Microsoft Office Professional oraz MS SQL Server 2014 wersje developerską.

Moje środowisko:
MS SQL 2014 Developer SP1 64bit

MS Office 2013  64bit

Windows 7 Profesional SP 1 64 bit

MS Data Mining Addin for MS Excel  ver 2012 sp2 64 bit
===========

Wystąpił u mnie problem:
"Object Reference not set to an instance of an object.

lub

Excel wyświetla komunikat że MS Data Mining Addin is causing errors

i proponuje jego wyłączenie.

===================

Dzwoniłem pod nr Pomocy Technicznej Microsoftu i polecono mi wykonać polecenie,

sfc /scannow

NIe zwróciło ono żadnych informacji o problemach

does not return nie zwraca żadnych naruszeń integralności.

Dostęp do danych ze strony MS SQL i dodatku   jest prawidłowo skonfigurowany (pod okiem trenera w czasie zajęć

z MS SQL Business Intelligence i Data Mining

=============

W sieci można znaleźć szereg zapytań o rozwiązanie tego problemu.
Występuje on prawdopodobnie dość często . Nie ma na nie odpowiedzi ze strony Microsoft ani innych użytkowników

Google:
data mining add in for microsoft excel, "object reference not set to an instance of an object" etc.

In a nutshell: C# Entity Framework Code First

In 33 min. the lecturer shows C# & Entity Framework Code First creating a simple Bicycle Rental database. One can see the use of Visual Studio to write Object Oriented C# code which creates and manages a database in MS SQL.

Time for "makers"? New industrial rev

http://iq.intel.pl/ruch-makersow-nowa-rewolucja-przemyslowa/

Coffee break - Divna Ljubojevic - Blagosloven jesi Gospode - Blessed be the Lord

Rachmaninof's version? Gives lots of energy!

Data Scraping in Excel

1. Find text on sheet, return value next to it:

In a column:
=OFFSET(INDIRECT(ADDRESS(MATCH("Value of contract:*";$A$1:$A$100;0);1));0;1)


But I do not know where in the sheet it shows up? In that case an udf will be handy:

Public Function ValueNext2Descr(description As String)
    
    Application.Volatile
  
    ValueNext2Descr = ActiveSheet.Cells.Find(What:=description, _
    After:=Cells(1, 1), LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Offset(0, 1).Value

End Function

2. Find a table located between two strings (and then paste it somewhere else):

With Sheets("Output1").Columns(1)
         Set foundStart = .Find("Table title")
         Set foundEnd = .Find("Some text under the table")
End With

        
Set foundStart= foundStart.Offset(2, 1)
Set foundEnd = foundEnd.Offset(-1, 6)

Range(foundStart, foundEnd).Copy
Worksheets("Output2").Range("C1").PasteSpecial
        
Application.CutCopyMode = False


'also worth considering: http://stackoverflow.com/questions/17890004/using-an-or-function-within-range-find-vba

RAD in Excel? Excel Framework, Parent Class Builder

Following the examples below one can develop a tool in Excel that generates the templates of classes with implemented encapsulation and collections for the specified data fields

 - Interesting article and tool that generates templates with parent-child relationship between classes in VBA, ie. single item class and its collection class. http://www.experts-exchange.com/articles/3802/Parent-Class-Builder-Add-In-for-Microsoft-Excel.html

- Another interesting example: Excel VBA Framework - an Excel tool that generates the classes for the fields entered in an Excel table.https://code.google.com/p/excel-vba-framework/downloads/list

Autofill a column next to my pasted data.

After I paste new rows into my data I need to fill down some formulas in an adjacent column. What if I would like to make it automatic? I came up so far with this code (in Worksheet module). I paste in my data to columns A through I and the formulas to be pulled down are in columns J through R:

Private Sub Worksheet_Change(ByVal Target As Range)

'Declare Variables
Dim FR As Long, LR As Long

Application.EnableEvents = False
Application.ScreenUpdating = False

'Find first and last row
FR = Sheets("Output3").Cells(Rows.Count, "J").End(xlUp).Row
LR = Sheets("Output3").Cells(Rows.Count, "A").End(xlUp).Row

If FR = LR Then Exit Sub

'Autofill
Range("J" & FR & ":" & "R" & FR).AutoFill Destination:=Range("J" & FR & ":" & "R" & LR)

Application.EnableEvents = True
Application.ScreenUpdating = True
   

Je fik stres :-)

Świetna komedia o dialogu międzykulturowym, stereotypach i resocjalizacji. "Jabłka Adama"

Coffee break... Et si tu n'existais pas...

Kiedyś nie wiedziałem o czym jest, byłem jeszcze w przedszkolu i kręciłem gałką słuchając egzotycznych dźwięków i tajemniczego szumu. Na średnich falach stare radio dziadka z magicznym okiem. I usłyszałem tę piosenkę i akurat ją zapamiętałem. ...Jeśli nie istniałabyś, to wiem, że też nie istniałbym patrząc jak smutny dzień, szary dzień...

Daily struggles with VBA/ACE SQL report in Excel

1. It does not copy down formulas that I paste in when I need it. On another occasion it does copy down the formulas against my will.

Application.AutoCorrect.AutoFillFormulasInLists = False 'or true if needed

It must be inserted just before the function that pastes in the formula into my listobject.table.

http://www.mrexcel.com/forum/excel-questions/580576-prevent-autofill-formulas-excel-tables-w-visual-basic-applications.html

2. The ACE SQL query execution results in column that is considered as text. I can live with that. But I must order it as numbers:

SORT BY IIf([Fieldname] Is Null, 0, Val([Fieldname]))

https://support.office.com/en-au/article/Sort-records-on-numeric-values-stored-in-a-text-field-502a36c4-2b6e-4453-91b6-7e842d234157 

3. I need my query to be iterated as many times as there are unique values in Excel. So far I found a discussion here: http://stackoverflow.com/questions/1676068/count-unique-values-in-excel
and an Excel formula here http://www.excel-easy.com/examples/count-unique-values.html 

4. Finally, I need to print out my report to pdf and under my table which spreads on multiple pages I need to include an extended description. My Table's column headings are repeating on each page, but I do not want to repeat it on my last page.

 I found two interesting solutions: Printing two sheets to one pdf - in the second sheet I am inserting all my caveats and interpretations to the report: http://stackoverflow.com/questions/14404650/save-multiple-sheets-to-pdf 

Here is a solution with a dynamic last row. It moves in relation to the last page division. http://stackoverflow.com/questions/28950832/in-excel-how-do-i-add-a-footer-to-the-last-page-only

I also need to check if the pdf file is open or not in the case I need to print it again under the same name:
http://stackoverflow.com/questions/25714915/check-if-a-certain-pdf-file-is-open-and-close-it

STEM education

http://learning-reimagined.com/11-billion-reasons-to-learn-stem/

How to: Visualisation of 38 511 800 Poles on a map.

I recommend (in Polish) the description of how to generate a map chart of Polish population at the level of borough/municipality: http://smarterpoland.pl/index.php/2013/05/poniewaz-mozemy-czyli-o-mapie-na-ktorej-widac-38-511-800-polakow/

VBA - Ticking a "Check all visible/filtered out" checkbox

I would like to have a tickbox at the top of the table to select all other tick'boxes within the table . I assume my tickbox should operate only if some data is filtered out and some rows hidden. Then my humble code looks like this.:

Private Sub CheckBox2100_Click()
  
  With ActiveSheet.ListObjects("tblCennik").ListColumns("Checkbox").DataBodyRange

    If .SpecialCells(xlCellTypeVisible).Count < .Rows.Count Then
    
    
    On Error Resume Next
    Application.EnableEvents = False
    
    .SpecialCells(xlCellTypeVisible).Value = Range("B5").Value
    
    Application.EnableEvents = True
    On Error GoTo 0   


    End If
    
  
  End With
    


End Sub

It needs improvement, perhaps to reset to unchecked when no checkboxes are selected in the table.. By the way, I copied somewhere this very valuable code that should go together in my notebook. It attaches checkboxes to cells in a column. I wish I could say thanks to the author, when I find the source I will definitely acknlwedge the author:

Sub UpdateList()
    Dim oCheck As OLEObject
    Dim rCell As Range
   
    'clear existing checkboxes
    For Each oCheck In Sheet1.OLEObjects
        oCheck.Delete
    Next oCheck
   
    'update the external data
    'Sheet1.QueryTables(1).Refresh False
   
    'add new checkboxes
    With Sheet1.ListObjects("tblCennik").ListColumns("Checkbox").DataBodyRange
        For Each rCell In .Columns(1).Cells
            If rCell.Row > .Rows(1).Row Then
                'rCell.RowHeight = 14 'this makes the checkbox look nicer
                With Sheet1.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
                    Top:=rCell.Top, Left:=rCell.Left, _
                    Height:=rCell.Height, Width:=rCell.Width)
                    .Placement = XlPlacement.xlMoveAndSize

                    .Object.Caption = ""
                    .LinkedCell = rCell.Address
                    .Object.Value = False
                End With
            End If
        Next rCell
    End With
   
End Sub

Narodowe Czytanie

Przy okazji czytania "Lalki" zaliczyliśmy miłe i niespodziewane spotkanie z prezydentem RP Andrzejem Dudą w Ogrodzie Saskim.

Warto wspomnieć przy tej okazji (i z ok. września 39) twórczość Wiesława Kuniczaka. Niedawno odnalazłem na strychu jego powieść "The Thousand Hour Day" kupioną w antykwariacie na starówce w Poznaniu gdy byłem w technikum. Nauczyłem się dzięki niej angielskiego... Może warto go przypomnieć, wznowić publikacje jego tłumaczeń na angielski Trylogii Sienkiewicza. Polecam artykuł na jego temat tutaj: http://www.zaginiona-biblioteka.pl/viewtopic.php?t=2488 (Nieznana epopeja o polskim Wrześniu   autor wpisu Krzysztof Zajączkowski:) :

"...dzieło, które stworzył, krytycy porównali do najlepszych utworów Hemingwaya i Tołstoja. Jak mówił: moje spojrzenie na wojnę nie pasowało do porządku, jaki komuniści zaprowadzili w Polsce. Dlatego do dziś wojenna trylogia Wiesława Stanisława Kuniczaka pozostaje w Polsce nieznana, a jej tłumaczenie nigdy nie znalazło wydawcy."

Polecam.

Refugees...

Scene from Baraka documentary.

A heavenly coffee break from Excel and MS SQL

Informacyjna dieta i zdrowe odżywianie

https://wszystkoconajwazniejsze.pl/lukasz-wrobel-pokaz-mi-jakie-informacje-wpuszczasz-do-swojej-glowy-a-powiem-ci-co-w-niej-masz/

Apel laika wyborcy do autorytetów i mediów (re PKB i dług publiczny).

Drodzy profesorowie, absolwenci znanych uczelni, SGH i London School of Economics, profesorowie fundacji Fullbrighta, znani dziennikarze. Drogi profesorze Leszku Balcerowiczu, profesorze Ryszardzie Petru. Proszę o pomoc i edukację w sprawie PKB i długu publicznego. Czy PKB, produkt krajowy brutto jest tym dla Państwa czym jest przychód netto przedsiębiorstwa? Obrót? Jeśli jest to miernik, który zupełnie nic nie mówi o stanie państwa, to dlaczego jest używany jako miernik stanu gospodarki. Czy dług publiczny Polski może być porównywany z długiem publicznym Niemiec czy Japonii? Piszą dziennikarze, że to nic groźnego mieć duży dług publiczny bo Niemcy czy Japonia mają więcej. Porównam do firmy. Gdy pożyczam by przejeść to jest to samo gdy pożyczam by zastosować dźwignię finansową, uzyskać większy dochód i jego częścią podzielić się z bankiem, który zaryzykował by dać mi pożyczkę? Gdy pożyczam jako przedsiębiorca, by kupić maszynę, by wynająć kolejną halę, liczę IRR, NPV, BEP. Czy w skali Państwa to się liczy jakimś wskaźnikiem efektywności pożyczonych i zainwestowanych w różne projekty pieniędzy? Jeśli odpowiedzi są: - nie PKB bez kontekstu nie mówi nic o dobrym lub złym stanie gospodarki, - nie, dług publiczny bez kontekstu nic nie mówi o rozwoju bądź zapaści gospodarki, to dlaczego te wskaźniki są używane przez autorytety ekonomiczne i dziennikarskie w opiniotwórczej prasie? Dobry analityk bankowy z łatwością obejrzy bilans firmy, rachunek zysków i strat i może ocenić, czy firma jest rentowna, czy też bankrutuje. Czy możemy liczyć na wskazanie właściwych mierników stanu polskiej gospodarki takich jakimi mierzy się przedsiębiorstwo? Jakie są wskaźniki, którymi powinniśmy się posługiwać zamiast PKB, zamiast porównań długu publicznego z Niemcami czy Japonią? Dlaczego Państwo nas nie bronicie przed fałszywymi ocenami, bo ewidentnie zamiast edukować media manipulują opinią publiczną, nie pozwalając podejmować obywatelom racjonalnych decyzji (informed decisions). Decyzji osobistych, zawodowych, wyborczych. Jako laik w kwestii ekonomii proszę lepszych od siebie o pomoc i wyjaśnienie. Jacek Kotowski

Classes in vba - summer holiday readings

http://www.cpearson.com/excel/Classes . aspx

www.ozgrid.com/forum/showthread.php?t=45621

visualbasic.ittoolbox.com/groups/technical-functional/vb-vba-l/inheritance-and-class-modules-869663

www.wiseowl.co.uk/blog/s237/classes.htm

m.youtube.com/playlist?lis=PLE750D2254A6CB125&itct

http://vbadeveloper.net/optionpricerclassesdesignvba.pdf

www.vb-helper.com/howto_interface_inheritance.html

www.bettersolutions.com/vba/VID973/LI743311912.HTM

Important keyword IMPLEMENTS
www.bettersolutions.com/vba/VID973/YN011720334.HTM

Adding functionality with classes
https://www.microsoftpressstore.com/articles/article.aspx?p=2225071&seqNum=2

www.cimaware.com/resources/article_39.html

www.wiseowl.co.uk/blog/s410/flappy-bird-excel-vba-class-modules.htm

bytes.com/topic/access/answers/195390-faking-inheritance-vba-remove-code-duplication

ramblings.mcpher.com/Home/excelquirks/snippets/classes

Coffee br: Array as object property in vba.

I looked at this post: http://www.mrexcel.com/forum/excel-questions/340910-arrays-object-properties-visual-basic-applications-3.html#post1684176 Other interesting examples to study http://www.ozgrid.com/forum/showthread.php?t=30333&p=173871#post173871 http://www.excelforum.com/excel-programming-vba-macros/508716-collection-vs-array-of-arrays-nszim.html https://msdn.microsoft.com/en-us/library/e1ad18x6(v=vs.90).aspx http://bytecomb.com/collections-of-collections-in-vba/ http://stackoverflow.com/questions/1402876/populating-collection-with-arrays http://www.wiseowl.co.uk/blog/s239/collections.htm http://www.wiseowl.co.uk/blog/s239/collections.htm http://codevba.com/help/collection.htm#.VbjjHzPdgp4 http://m.youtube.com/watch?v=YIsZJ8wWokk http://www.functionx.com/vbaexcel/Lesson12.htm OT: interesting vba course on Arrays: http://www.snb-vba.eu/VBA_Arrays_en.html http://stackoverflow.com/questions/29130153/autofilter-by-inputting-array-by-selecting-a-range-of-crieteria-vba-excel Must read: http://www.cpearson.com/excel/Classes.aspx https://blog.udemy.com/vba-class/

I am so tired...

SQL: who had best result this month.

To explore next week answer to my question: https://quorum.akademiq.pl/discussion/comment/8027#Comment_8027

Coffee break: Chart Events exploration

Using events with embedded charts:
https://msdn.microsoft.com/en-us/library/office/bb211434%28v=office.12%29.aspx

https://books.google.co.uk/books?id=HxhXwdUSTe0C&pg=PA607&lpg=PA607&dq=excel+embedded+chart+with+events&source=bl&ots=VZ5MvmFZUl&sig=SGV0NewIHjD4Z9Vb9C0n4w7unEM&hl=en&sa=X&ei=uAhtUfv8Fe-k0AXm1oHQBg#v=onepage&q=excel%20embedded%20chart%20with%20events&f=false

http://www.mrexcel.com/forum/excel-questions/697254-how-create-mouse-interactive-chartobject.html

http://www.excel-easy.com/vba/examples/programming-charts.html

http://stackoverflow.com/questions/7431586/catch-event-on-chart-click

Moje pytanie na Quorum Altkom: https://quorum.akademiq.pl/discussion/comment/8021#Comment_8021

R in MS SQL 2016 this summer !!! ..

This was earthbreaking news. How could I have overlooked...
http://www.computerworld.com/article/2923214/big-data/sql-server-2016-to-include-r.html

http://blog.revolutionanalytics.com/2015/05/r-in-sql-server.html

Brief information for advanced number crunchers on what R offers:
http://www.computerworld.com/article/2882819/learn-how-to-crunch-big-data-with-r.html

Do you need to know R and big data? (Polish article in Employment section of Gazeta Wyborcza)

http://gazetapraca.pl/gazetapraca/1,90440,18340280,badacz-danych-poszukiwany.html

Coffee reads: 
 http://www.analyticsvidhya.com/blog/2015/02/step-step-guide-learn-time-series/
http://www.analyticsvidhya.com/blog/2015/02/exploration-time-series-data-r/

HAM: easy SDR RX project for beginner?

After Smallwonder Labs SW40 this is my new challenge dream. I want to build this! :
http://yu1lm.qrpradio.com/DR2-2%20HF%20SDR%20RX-YU1LM.pdf
and here is the continuation of RockMite: http://qrpme.com/?p=product&id=RM4
I shall not forget ab. Miss Mosquita http://www.qrpproject.de/UK/missmosquita.htm
and Lidia http://www.sp5ddj.pl/Lidia%2040%20DC%20Receiver/Lidia%2040%20-%20DC%20Receiver.htm

Coffee break: R in Excel: RExcel and R.net revisited

I had a look what changed:
RExcel - I installed it on 64bit MSWin 7 with old version of R (2.15.0) with a success.. Tested the example files, they work correctly. Failed to install it with R 3.1.x. Apparently it is possible to install it with R 3.0
http://stackoverflow.com/questions/16651837/rexcel-in-r-3-0-x

ExcelDNA - how to use:
https://github.com/Excel-DNA/Samples/tree/master/UsingRDotNet
http://stackoverflow.com/questions/29279908/how-to-get-exceldna-work-with-r-net
It is being developed very actively - 64bit version issued a couple of days ago.

Time to learn R.
Update examples of use in C#
http://quantlabs.net/academy/very-nice-my-c-program-calls-r-code-through-the-r-net-package-dancing-in-the-streets/
https://psychwire.wordpress.com/2011/06/19/making-guis-using-c-and-r-with-the-help-of-r-net/
https://psychwire.wordpress.com/2011/06/25/importing-and-displaying-a-data-frame-with-c-and-r-net/
https://psychwire.wordpress.com/tag/r-2/
http://www.codeproject.com/Articles/25819/The-R-Statistical-Language-and-C-NET-Foundations

Excel: Vector Map of Polish Counties, Shapes for Choropleths

See these tutorials first:
http://www.clearlyandsimply.com/clearly_and_simply/2012/12/create-excel-choropleth-maps-from-shape-files.html
http://www.clearlyandsimply.com/clearly_and_simply/2009/08/build-your-own-choropleth-maps-with-excel.html

Then download shapefile for Poland from here:
http://www.gadm.org/country

Next, use online service to convert shp to svg file:
 http://indiemapper.com/app/ (By the way, you can open svg as xml in Excel and extract a table with columns for  Province, County or Major Town - you may need it. )

Convert svg file into emf in Inkscape (free and open source equivalent of InDesign)

You can now open emf in Excel or PowerPoint and ungroup the vector image into individual shapes for each county. Now it will be possible to use VBA to give them colors, values, labels etc.

More info https://pl.wikipedia.org/wiki/Powiaty_w_Polsce
Translation issues: http://serwistlumacza.com/content/view/27/32/

Another option: http://www.exprodat.com/blogs/blog_Extracting-Vector-Data-from-PDFs.htm using
http://ksng.gugik.gov.pl/pliki/mapa_administracyjna_polski_2012.pdf

Insomnia: Playing with QueryTables In Excel

Playground: Write a code for quick creating and refreshing a QueryTable in Excel - data source is one table (ListObject.Table, one user creates with Ctrl+T) - data is in an excel file - data source relative - button to refresh a query.

Sub main()
  Call mkQryTbx([Table1[#All]], [H2])
End Sub

Sub mkQryTbx(DataRange As Range, OutputRange As Range)
 
 Dim dtaRange As String: dtaRange = "[" & ActiveSheet.Name & "$" & DataRange.Address(False, False) & "]"
  
'create QueryTable
  
  With ActiveSheet.QueryTables.Add("OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName _
    & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";", OutputRange)
    .CommandText = "SELECT name, number FROM " & dtaRange & " WHERE number = 1;"
    .Name = "ExternalData"
    .BackgroundQuery = False
    .Refresh False
  End With

'create an Update button 

  Dim t As Range: Set t = ActiveSheet.Range("A1")
   Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
   With btn
     .Name = "btnRefresh"
     .OnAction = "btnS"
     .Caption = "Refresh"
   End With

End Sub

Sub btnS()
    ActiveSheet.Range("H2").QueryTable.Refresh BackgroundQuery:=False
End Sub

http://dailydoseofexcel.com/archives/2008/06/24/create-a-querytable-from-an-excel-source-in-vba/ Other reading against using QueryTables (why?) http://itknowledgeexchange.techtarget.com/beyond-excel/say-goodbye-to-querytables/ Insert button: http://stackoverflow.com/questions/4559094/how-to-add-a-button-programmatically-in-vba-next-to-some-sheet-cell-data

Coffee break: transliteration to cyrillic

Occasionally one tries to remember some Russian and write to a friend or a customer in their native alphabet. But how to write Russian ж and where to find it on a Russian keyboard? Would it be simpler to write  zh and have it converted on the fly?

The easiest way is to use a (google:) transliterator (you just type phonetically, eg. zh and get a desired letter). There are plenty of solutions online like http://www.benya.com/transliterator/ (incl. firefox plugin), http://translit.cc/. I also tried Typus 4.0, a program enhancing the actions of standard keyboards with transliteration but it was not stable on my computer.

Early Morning Cafe - The role of an elected politician.

"You are not only an elected representative, you are also an employee, our employee. We are paying you. And you must honestly and diligently work for us, represent our interests, inform us in the councils, write the memos/minutes when you meet (to discuss our business), not obstruct, not wipe under the carpet. This is expected by the inhabitants, your employers." Overheard early in the morning, Elżbieta Jaworowicz's "Sprawa dla reportera" (the case for a reporter) show.

VBA classes, collections: YT videos to watch carefully and excercise this week.

To study this week, classes and collections in Excel By Krellon: https://www.youtube.com/watch?v=WqyroALr2PE By Dinesh Kumar Takyar https://www.youtube.com/watch?v=B8ceYx_ZLPY WiseOwlTutorials https://www.youtube.com/watch?v=MzydF6KOGy8 Visual Basic Tutorial.NET https://www.youtube.com/watch?v=N9GzVLfy5Ok

Coffee break, recordset

Ciekawy fragment kodu do zrzucenia danych z recordsetu do excela.

R = 0
If Not (Rs.BOF And Rs.EOF) Then
   Rs.MoveFirst
   While Rs.EOF <> True
      Range("A1").Offset(R, 0).Value = Rs.Fields(0)
      Range("B1").Offset(R, 0).Value = Rs.Fields(1)
      Range("C1").Offset(R, 0).Value = Rs.Fields(2)
      Range("D1").Offset(R, 0).Value = Rs.Fields(3)
      Rs.MoveNext
      R = R + 1
   Wend 

Zadanie na kiedyś... - przerobić, by kod wiedział ile kolumn trzeba stworzyć (Rs.Fields.Count?) - przerobić, by kod wklejał do ListObject.Table raczej niż do komórek. - przerobić, by kod zwracał nagłówki do ListObject. - przerobić, by kod czyścił Listobject.Table jeśli już istnieje.

Coffee break: My personal (project) website on corporate server? Without headaches to IT?

A small idea: If there is a couple of MB on corporate fileserver, accessible to all we can share information using a neat TiddlyWiki - single webpage application. No servers are involved, no need to hassle IT guys for permissions. Tittly Wiki is a self contained html/js website. You can edit, publish, create wiki-like links to better organize knowledge and save it. Voila, the update is visible to all your friends. See: http://tiddlywiki.com http://blog.jeffreykishner.com/2014/01/17/a-tiddlywiki-filter-to-list-due-dates.html Getting Stuff Done implementation for self/micro-project management. http://gsd5.tiddlyspot.com/ Calendar for older, classic, version http://remindermacros.tiddlyspot.com/ BTW: Related interesting topic on Wikipedia https://en.wikipedia.org/wiki/Single-page_application Example: password generator http://angel.net/~nic/passwd.current.html

Coffee break: Classes w VBA...

I do not know how to use classes  in VBA yet. do I need to know?

Google is quite helpful:


What benefit from using, discussions for studying ...
http://stackoverflow.com/questions/2161666/what-are-the-benefits-of-using-classes-in-vba
http://stackoverflow.com/questions/2121093/excel-vba-programming-with-arrays-to-pass-them-or-not-to-pass-them/2135003#2135003

Learn:
http://www.cpearson.com/excel/classes.aspx
http://ramblings.mcpher.com/Home/excelquirks/snippets/classes
http://ramblings.mcpher.com/Home/excelquirks/classeslink
http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=160:excel-vba-custom-classes-a-objects-class-modules-custom-events&catid=79&Itemid=475&showall=&limitstart=1

Wise Owl YT course:
https://www.youtube.com/watch?v=MjbmsVDnAL0

Maybe it is good for storing data?

https://www.daniweb.com/software-development/visual-basic-4-5-6/threads/131159/class-modules-and-arrays

Bookmarklet opens a stream player in a popup.

Work gets tedious occasionally and I get motivated with Orthodox Church chants.
And I would like to use the player online:

Is there a way to open my bookmark in a popup.

http://stackoverflow.com/questions/13536058/create-a-javascript-popup-by-using-the-address-bar

Here is an example of a bookmarklet (paste it in a bookmark) to open Ancient Faith radio in a popup.

javascript:void(window.open('http://www.ancientfaith.com/player/afmusic.php','popupwindow','width=320,height=240'))

Paste to end of table.

Copy... paste, data scraping ...
- pasting add to end of table

- and learn to reuse the same code.

Sub PasteEOTable(NazwaArkusza As String, NazwaTabeli As String)

Dim mojaTabela As ListObject: Set mojaTabela = Worksheets(NazwaArkusza).ListObjects(NazwaTabeli)

With mojaTabela
  If .DataBodyRange.Rows.Count = 1 And .DataBodyRange.Text = "" Then .DataBodyRange.Rows.Delete
   .ListRows.Add AlwaysInsert:=True
  Worksheets(NazwaArkusza).Paste Destination:=.DataBodyRange.Cells(.DataBodyRange.Rows.Count, 1)
End With

End Sub


Sub main()

Call PasteEOTable("Arkusz1", "Tabela2")

End Sub

More to research: It seems that the first (or the 0 row) serves the purpose of saving the formatting and formules for columns. If we delete the first row, it is important to remember how to bring back all the formulas and number or date formatting.

Kawa poranna: Edytuj tabelę ms sql w ... excelu :-)

Wow. Jest to calkiem możliwe: http://www.toadworld.com/platforms/sql-server/w/wiki/10392.editing-an-sql-server-table-in-excel

Fuzzy matching columns revisited.

Have you ever had to join two tables with spelling and typing errors, variants of the same name etc.? Try an UDF (user defined function in Excel) using NGrams and DiceCoefficient... my recommendation:
http://www.niftytools.de/2015/03/fuzzy-string-matching-excel/

Another option is to use an excelent vba macro which uses Levenstein and several other routines for fuzzy matching. http://www.mrexcel.com/forum/excel-questions/195635-fuzzy-matching-new-version-plus-explanation.html

Moreover, it is often the case that we want to join tables on strings that exist within names in a column of another table. Then you may use the following formula (array formula, press Ctrl+Shift+Enter):

{=INDEX(kolumna_wartosci;MATCH(FALSE;ISERROR(SEARCH(kolumna_przeszukiwana;szukana_wartosc));0))}

Other options... search google for fuzzy lookup udf: np. stackoverflow: http://stackoverflow.com/questions/13291313/matching-similar-but-not-exact-text-strings-in-excel-vba-projects
--------------
In MS SQL you can try using SimMetrics library and compare various routines:   http://anastasiosyal.com/POST/2009/01/11/18.ASPX

And if we look for strings (in a column in TableB) in a column with long names  in TableA, we can try:

SELECT  TableA.descr as opis, max(TableB.Descr) AS major, b
   FROM TableB
   RIGHT JOIN TableA
   ON  TableA.Descr LIKE ("*"+TableB.Descr+"*")
   GROUP BY  TableA.descr,b;

Lektura na dziś: migracja MS Access to MS SQL

Temat: migracja do MS SQL:
https://www.youtube.com/watch?v=bZX-CwRZE1w

Access VBA:
https://www.youtube.com/watch?v=kogGwRIHH6o&list=PLYMOUCVo86jEeMMdaaq03jQ_t9nFV737s

Temat unbounded forms:
http://www.databasedev.co.uk/unbound-forms-add-data.html
http://oakdome.com/programming/MSAccess_Unbound_Forms_VBA.php
http://bytes.com/topic/access/answers/872993-unbound-form-how-get-next-record

Excel Arduino commander :-)

Ciekawostka: 
http://www.robertovalgolio.com/sistemi-programmi/arduino-excel-commander
http://www.robertovalgolio.com/sistemi-programmi/ecosystemofthings
http://mikmo.dk/gobetwino.html

Dzis w internetach: T-sql dynamic columns, klasy w vba

T-sql dynamic pivot columns - sklejanie zapytan, zapytania dynamiczne
http://stackoverflow.com/questions/14797691/dynamic-pivot-columns-in-sql-server

Klasy w VBA?
http://stackoverflow.com/questions/118863/when-to-use-a-class-in-vba
http://www.cpearson.com/excel/classes.aspx

Wklejanie formul tablicowych za pomoca vba
https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/
http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

Range to array:
http://www.sitestory.dk/excel_vba/arrays-and-range-formulas.htm


Fotografia: Autoexposure lock with spot metering
http://www.magiclantern.fm/forum/index.php?topic=4935.0

Gmail as a default mail client in Windows.

It is possible after installing tvhgooglemapi ( http://jankeirse.github.io/tvhgooglemapi/, Java required) and setting "less secure" access of applications, to Google ie. requiring only the email address and password. https://www.google.com/settings/security/lesssecureapps

Calling a default mail client by a windows application will start the default brawser and add the created message to drafts.

For instance one can write a VBA macro in Excel that sends an email with a browser and Gmail account:  https://msdn.microsoft.com/en-us/library/office/aa203718%28v=office.11%29.aspx#odc_mailasinglesheet  ...

Lektura na dzis: 1 C#,CLR stored procedures 2. Pivot to... ansi sql 3. r...

1. http://www.sqlteam.com/article/writing-clr-stored-procedures-in-charp-introduction-to-charp-part-1
2. http://stackoverflow.com/questions/3120835/how-to-pivot-rows-into-columns-custom-pivoting
http://stackoverflow.com/questions/13953134/convert-access-transform-pivot-query-to-sql-server
http://www.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query
http://stackoverflow.com/questions/13953134/convert-access-transform-pivot-query-to-sql-server
http://stackoverflow.com/questions/5065027/convert-mdb-to-sql-server/5065606#5065606
3. https://www.datacamp.com/courses/free-introduction-to-r oraz https://www.datacamp.com/courses/kaggle-tutorial-on-machine-learing-the-sinking-of-the-titanic?utm_source=kaggle-ml-launch&utm_medium=blog&utm_campaign=kaggle-ml-launch, http://www.r-bloggers.com/search/excel
4. SQL w Excelu:
http://stackoverflow.com/questions/17678856/querying-a-sql-server-in-excel-with-a-parameterized-query-using-vba
http://stackoverflow.com/questions/19755396/performing-sql-queries-on-an-excel-table-within-a-workbook-with-vba-macro
http://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/
http://www.codeproject.com/Tips/147331/Reading-Excel-file-in-SQL-Server
http://stackoverflow.com/questions/375991/import-excel-spreadsheet-columns-into-sql-server-database
http://stackoverflow.com/questions/14038020/how-to-deal-with-excel-file-in-sql-query
http://superuser.com/questions/813077/execute-sql-query-with-vba-and-fill-sheet-with-data
http://www.mrexcel.com/forum/excel-questions/617788-visual-basic-applications-connect-sql-server.html

Bardzo proste odpytywanie tabel w excelu w vba: http://analysistabs.com/excel-vba/ado-sql-macros-connecting-database/