pandas calculate return by shifting timeseries index

 usually, we calculate the return with next row data by simply using df.Close.shift()

but if we want shifting by a specific time period, can try:

df['ret15min'] = (df.Close.shift(-15, freq="min") -df.Close)*100/df.Close

Install without root access / for current user

 You can use conda to install mysql client!

https://stackoverflow.com/questions/36651091/how-to-install-packages-in-linux-centos-without-root-user-with-automatic-depen/52561058#52561058

recreate / cloning conda env with limited internet access

• https://www.anaconda.com/blog/moving-conda-environments
• https://docs.conda.io/projects/conda/en/latest/user-guide/tasks/manage-environments.html

Generate FPS QR Code with customized amount

1. Scan an existing QR code at https://zxing.org/w/decode.jspx https://qrcoderaptor.com to get the text content
2. Remove the last 4 characters, which is the checksum (CRC), from the text content
3. Spot the substring that contains the amount from the text content
4. Replace the amount substring with new amount using the *same number* of characters, for example, replacing 
• 1755.00
• with
• 0050.00
• will update the amount from $1,755 to $50
5. Calculate the new checksum
1. http://www.sunshine2k.de/coding/javascript/crc/crc_js.html
1. width: CRC-16
2. parametrization: Custom
3. Polynomial: 0x1021
4. Initial Value: 0xFFFF
5. Input Data: String
6. Paste the updated text content from above
7. Click Calculate CRC
6. Generate new QR code
1. https://www.qrcode-monkey.com/#text
2. Paste the updated text content appended with the new 4-character CRC
3. Click Create QR Code

Ref: FPS QR code specification at https://www.hkma.gov.hk/media/eng/doc/key-functions/financial-infrastructure/infrastructure/retail-payment-initiatives/Common_QR_Code_Specification.pdf

send email html content with inline image

• preferred way:
• script to generate html content with image encoded as base64 text
• <img src='data:imag/png;base64,....'/>
• base64.b64encode().decode("ascii") to get that ... string from image
• single html file to include all the html and images, etc. 😀
• cat that html and pipe to mutt -e "set content_type=text/html" to send the email

• however, outlook does not support inline base64 image 😞
• one way to workaround is to pipe the html to a python script that
• extract that base64 string (... above)
• replace html code with <img src='cid:123' />
• create image object with
• image = MIMEImage(base64.decodebytes(....encode("ascii")), _subtype=<png/jpg/etc>)
• reference this image by
• image.add_header('Content-ID', '<123>')
• send the emails as shown in this example
• https://docs.python.org/3/library/email.examples.html
• To send from windows outlook client rather than directly via smtp server:
• https://stackoverflow.com/questions/44544369/i-am-not-able-to-add-an-image-in-email-body-using-python-i-am-able-to-add-a-pi

url to pdf on linux

yum install wkhtmltopdf
To manipulate the pdfs, use pdfunite / pdfseparate from
yum install poppler-utils

centos minimal

install centos 8 minimal

install google-chrome from google repo, which should install all the X dependencies

additional language support for web page display

> yum groupinstall Fonts

To start chrome full screen
> X& DISPLAY=:0 google-chrome --window-size=1920,1080&

if sound is not already working,
> yum install alsa-utils
unmute everything with
> alsamixer
https://wiki.archlinux.org/index.php/Advanced_Linux_Sound_Architecture#Disabling_auto_mute_on_startup

Excel: if A1 in ("a", "b", "c)

https://exceljet.net/formula/cell-equals-one-of-many-things

running graphical applications without desktop env

1. install linux w/ desktop environment
2. start run level 3
3. $ X &
4. $ DISPLAY=:0 google-chrome
5. "You can switch from the X server and the framebuffer by using CTRL+ALT+F1 and CTRL+ALT+F7"

https://superuser.com/questions/407043/is-it-possible-to-run-graphical-applications-such-as-firefox-without-installing

save / print pdf without margin

Why?
pdf, including ones at research site ssrn, in general have huge margin.  when you wanna print 2 pages per sheet, the text become smaller with a lot of unnecessary white space for the margin

What do you need?
pdfcrop, which is available at ubuntu, but not centos7 afaik at the time of writing

How?
pdfcrop -h
pdfcrop <input.pdf> <output.pdf>

Appendix:
What if you don't have ubuntu?
if you have centos7 running on virtualbox with windows host, here's what I did:

1. download ubuntu 18 desktop installation iso
2. Virtualbox, existing vm, storage, optical drive
1. select the downloaded iso
2. check Live CD/DVD box
3. start VM
4. choose "Try ubuntu" after it boots from disc
5. Follow https://askubuntu.com/questions/378558/unable-to-locate-package-while-trying-to-install-packages-with-apt to update package download source
6. Open a terminal in ubuntu
1. sudo apt-get install texlive-extra-utils
2. pdfcrop <input.pdf> <output.pdf>
7. ubuntu map windows host's shared drive
1. netstat -nr to see window host's ip address from ubuntu
2. ubuntu "Files" app, which has a folder icon
3. connect to Server by entering smb://<windows ip>
8. drag and drop <output.pdf> to shared folder

Where is the source file of an imported module?

from PackageABC.ParentModule import ChildModule

import inspect
print(inspect.getsourcefile(ChildModule))

#which would be in one of these paths
import sys
for p in sys.path:
    print(p)

kdb schema idea for market depth from bid ask streams

select lUnixts:last unixts, vwap:siz wavg price, desc price, siz idesc price, siz by unixts, sym from lp where side=`BID

basically, for each timetamp, sym, there's a list of prices, the corresponding list of size, liquidity provider, etc.

see http://code.kx.com/wiki/Cookbook/ProgrammingIdioms#How_do_I_extract_regular-size_vwap_series.3F for explanation

FD whitepapers:

1. http://code.kx.com/qref/wp/sample_aggregation_engine_for_market_depth.pdf
2. http://code.kx.com/pages/db/DB_Order_Book_a_kdb+_Intra-Day_Storage_and_Access_Methodology.pdf

How to select the row value associated with the max / min of a column?

http://code.kx.com/wiki/Cookbook/ProgrammingIdioms#How_can_I_extract_the_time_of_the_lowest_and_highest_prices.3F

one way to pivot table

trade table:

stock side amount
----------------------
`ibm b 100
`ibm s 200
`appl b 300

-- sql server syntax
select stock, sum(iif(side='b', amount, 0)) as b, sum(iif(side='s', amount, 0)) as s
 from trade
 group by stock;

stock b s
------------------
`ibm 100 200
`aapl 300 0

Column names as parameters to functions

http://code.kx.com/wiki/Cookbook/ProgrammingIdioms#Column_names_as_parameters_to_functions

How many times do u expect to roll a die b4 getting two consecutive sixes?

No, it's not 36!

http://www.wikihow.com/Calculate-an-Expected-Value#Calculating_an_Expected_Number_of_Coin_Flips_for_a_Specific_Result_sub

Conditional Probability - a visualization

No need to remember the formula.  Instead, understand it and u can derive it.

Looking at the diagram above, 

• Omega is the entire probability space.  [rectangle]
• Event A is a subset of Omega with probability of happening P(A). [left circle]
• Event B is a subset of Omega with probability of happening P(B).  [right circle]
• A intersect B is when A and B both happening. [middle ellipsis]

P(B|A) is 

• conditional prob of B given A
• ie knowing that A has happened, it is the chance of B happening

What does it really mean?

1. knowing that A has happened -> the prob space has shrunk from Omega [rectangle] to P(A) [red circle]
2. the chance of B happening left is A intersect B [middle ellipsis]
3. note that we don't look at the rest of P(B) [outside of red circle] anymore, since it's outside of the realm of possibility knowing that A has happened.

Picturing the diagram in your mind, you will figure out that

• Given A has happened, chance of B happening is [middle ellipsis] divided by [red circle]
• ie P(B|A) = P(A intersect B) / P(A)

source:
https://courses.edx.org/courses/course-v1:MITx+6.008.1x+3T2016/courseware/1__Probability_and_Inference/conditioning_on_events/

kdb group by weekday, hour

select count i by weekday:-1+`int$date mod 7, "n"$0D01:00:00.0 xbar date+time from table

Note: 

1. `int$date cast date to the number of days since year 2000
2. by hour can be done with time.hh, but the above will actually return a time column instead of int column

one-line if then else

C++
<condition> ? <if true return> : <else return>

Python
<if true return> if <condition> else <else return>

example:
rsl = f[len(prefix):] if f.startswith(prefix) else f

https://mail.python.org/pipermail/python-list/2010-July/581933.html

mysql pivot table with dynamic columns

http://buysql.com/mysql/14-how-to-automate-pivot-tables.html