<jupyter_start><jupyter_text>SPI Intradayprices<jupyter_code>#for ishares

#  https://www.six-swiss-exchange.com/funds/security_info_de.html?id=CH0237935652CHF4
from pandas import read_csv, concat, DataFrame, Series, ExcelWriter
from math import copysign
from pprint import pprint

%matplotlib inline
def calc_faktor(df, iday_fonds, iday_idx):
    '''
    calculate faktor to minimize square deviation )
    '''
    if iday_fonds == iday_index: return 1
    print(iday_fonds, ' ', iday_idx)
    cnt = 0
    inc  = 20
    faktor = 1
    diffold = 1e9
    while (cnt < 100) and (abs(inc) > 0.001):
        cnt += 1
        diff = ((faktor * df[iday_fonds]-df[iday_idx])**2).sum()
        if diff > diffold:
            inc = -inc/2
            #print('True',end=' ')
        faktor = faktor + inc
        #print('{0:4.0f} faktor:{1:12.8f} inc:{2:10.6f}   diff:{3:.0f} diffold {4:.0f} {5:.0f}'
        #     .format(cnt, faktor, inc, diff, diffold, diff-diffold))
        diffold = diff
    return faktor
# create list of .csv filenames
fname = 'info_market_data_download_id(x).csv'
fnamelist = [fname.replace('x',str(i)) for i in range(1,4)]

# read intraday prices from csv files and write into dataframe
prcs_list = {}
iday_index = '' 
names = []
for i, fn in enumerate(fnamelist):
    temp = read_csv(fn, skiprows=2, sep=';',index_col=0)
    temp = temp.Price
    name1, name2 = read_csv(fn, nrows=1, encoding='latin-1').to_string().split('\n')
    name1 = name1.strip(' ').replace(' ', '-').replace('\xa0','')
    name2 = name2[-11:-1]
    name = name1+'-'+name2
    names.append(name)
    temp.name=name
    temp.index.name = None
    prcs_list[i] = temp
    if 'CH0009987501' in name: iday_index = name # remember SPI index
id_prcs = concat(prcs_list,axis=1)
id_prcs.columns = names
id_prcs.index = ('2016-09-30'+id_prcs.index).to_datetime()
# create dataframe with full matrix
id_prcsclean = id_prcs.interpolate().ffill().bfill()
pprint(names)
# adjust to same scale
id_prcsadj = id_prcs
id_prcsadj[iday_index] = id_prcsadj[iday_index].interpolate().ffill().bfill()
for name in names:
    ff = calc_faktor(id_prcsclean, name, iday_index)
    id_prcsclean[name] *= ff
    id_prcsadj[name] *= ff    
# create excel file
writer = ExcelWriter('auswertung.xlsx')
id_prcs.to_excel(writer,'id_prcs')
id_prcsclean.to_excel(writer,'id_prcsclean')
id_prcsadj.to_excel(writer,'id_prcsadj')
writer.save()
id_prcsclean.plot(figsize=(15,4))
id_prcsadj.plot(figsize=(15,4))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14761331728840754, 'fraction_numerical': 0.03529883674288006, 'mean_word_length': 3.909448818897638, 'pattern_counts': {'":': 0, '<': 6, '<?xml version=': 0, '>': 7, 'https://': 1, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '206232', 'license': 'MIT', 'n_tokens_mistral': 1038, 'n_tokens_neox': 992, 'n_words': 194, 'path': 'ReadCSV_intraday.ipynb', 'repo_name': 'Fintool/Indexfonds_vs_ETF', 'size': '2493'}