תבנית לניהול תיק השקעות

[Ilia]

תודה @liranviper על השיתוף, יופי של כלי.

ניסיתי להוסיף ני״ע של ishares ובמבט ראשון נראה שאין תמיכה בשליפת נתונים של ני״ע אלו, האם זה נכון?
אני מניח שמדובר פה בבורסת ישראל, וגם ניסיתי להחליף את ה-URL של מאיה (maya.tase.co.il/fund/X ל- maya.tase.co.il/foreignetf/X), אבל זה עדיין לא הסתדר.

@ramiv, ראיתי ששאלת בנוגע לני״ע של ishares, האם צריך לעשות שינוי כלשהו כדי לגרום לזה לעבוד?

תודה רבה!

 

[RustyJ]

תודה @liranviper על השיתוף, יופי של כלי.

ניסיתי להוסיף ני״ע של ishares ובמבט ראשון נראה שאין תמיכה בשליפת נתונים של ני״ע אלו, האם זה נכון?
אני מניח שמדובר פה בבורסת ישראל, וגם ניסיתי להחליף את ה-URL של מאיה (maya.tase.co.il/fund/X ל- maya.tase.co.il/foreignetf/X), אבל זה עדיין לא הסתדר.

@ramiv, ראיתי ששאלת בנוגע לני״ע של ishares, האם צריך לעשות שינוי כלשהו כדי לגרום לזה לעבוד?

תודה רבה!

עובד גם מול קרנות בלאקרוק הישראליות, בדיוק כמו שבגליון עובד לתעודות ישראליות רגילות.
מחיר היחידה הוא לוקח אצל sponser, את דמי הניהול לוקח אצל bankleumi

 

[Ilia]

תודה על התשובה.

שאלה נוספת ברשותכם.
מילאתי את טאב ה״תיק״ עם כלל הניירות והסכומים, וכעת התיק מאוזן.
מהו סדר הפעולות הנדרש ברגע שיש לי סכום חדש אותו אני רוצה להשקיע?

לפי ההבנה שלי אני צריך למלא בטאב ״משתנים״ את ה״אפיק מניות - סכום לחשב ממנו קניית נ"ע״ (וגם אותו דבר עם אפיק אג״ח?), ואז בטאב ה״תיק״ להדליק את כל הצ׳קבוקסים של ״האם לקנות״ ליד כל נייר.
כשעשיתי את זה קיבלתי רק 3 שורות (כאשר יש לי 4 ניירות בתיק) ורק באחת מהם מופיע כמות לקניה (אחרים או ריקים או עם !REF#).
מה אני מפספס?

לינק של התיק לדוגמא - https://docs.google.com/spreadsheets/d/13eRae_aA9A-1n-tjJjKgh1yMavaVvX-aG73s-r5_MsQ/edit?usp=sharing

המטרה שלי כמובן היא להזין סכום X ולקבל Y פקודות (Y כמספר הניירות בתיק) עם נתונים של כמה יחידות לקנות מכל נייר על מנת שהתיק ישאר מאוזן.

תודה רבה לעונים.

 

[leo_123]

לירן שלום, אני משתמש בחוזה שלך כרפרנס לחוזה שאני כותב בימים אלו עם עצמי.
שמתי לב לתאריך כתיבת החוזה - ינואר 2016. אנחנו נמצאים במרחק 4 שנים מנקודת הכתיבה. תוכל לעדכן איך התוכנית מתקדמת? באילו קשיים נתקלת ביחס לחוזה שכתבת? אולי טיפים לעריכה עדכנית יותר לאור הניסיון.
תודה ובהצלחה!

 

[liranviper]

לירן שלום, אני משתמש בחוזה שלך כרפרנס לחוזה שאני כותב בימים אלו עם עצמי.
שמתי לב לתאריך כתיבת החוזה - ינואר 2016. אנחנו נמצאים במרחק 4 שנים מנקודת הכתיבה. תוכל לעדכן איך התוכנית מתקדמת? באילו קשיים נתקלת ביחס לחוזה שכתבת? אולי טיפים לעריכה עדכנית יותר לאור הניסיון.
תודה ובהצלחה!

הי, אני שמח שהוא עוזר לך!
זה פוסט על תבנית השקעות.. יש פוסט יעודי לתיק שלי, שממנו גם הסולידית יצרה את הפוסט אורח. עניתי על אותה השאלה שם, אבל אתה מוזמן לשאול עוד שם אם משהו לא ברור

 

[Wen Lambo]

תודה @עומר @liranviper ושאר השותפים למאמץ.
תבניות וגליונות נהדרים יש כאן
למעוניינים, כתבתי סקריפט קטן בgoogle sheets שמביא את שערי ניירות הערך שנסחרים בבורסת תל אביב לפי תאריכים (בדומה לפונקציית GOOGLEFINANCE לני"ע ישראליים). המידע מובא מביזפורטל.

דוגמאות לשימוש:

=IMPORT_TLV("1150184")

מביא את השער (הנעילה) המעודכן האחרון של הנייר 1150184

=IMPORT_TLV("1150184",DATE(2019,1,1))

מביא את השער של הנייר בתאריך 1.1.2019

=IMPORT_TLV("1150184",DATE(2019,1,1),DATE(2019,12,31))

מביא את כל השערים של הנייר בין התאריכים 1.1.2019 - 31.12.2019

* אם אין תוצאות עבור תאריך מסויים זה אומר שאין תיעוד עליו בתאריך זה (הנייר לא היה קיים או שלא היה מסחר באותו יום ואפשר להניח שהשער נותר זהה לשער הכי קרוב לפניו)
כדי להשתמש יש להכניס בגליון את הסקריפט: Tools > Script Editor לתת שם חדש לפרוייקט וללחוץ על save.

הקוד:

function IMPORT_TLV(ticker, start_date, end_date) {
  var url = 'https://www.bizportal.co.il/forex/quote/ajaxrequests/paperdatagraphjson?paperID=' + ticker;
  var jsondata = UrlFetchApp.fetch(url);
  var object   = JSON.parse(jsondata.getContentText());
  var daysArray;
  var dateToString = function(d) {return [('0' + d.getDate()).slice(-2),('0' + (d.getMonth() + 1)).slice(-2), d.getFullYear().toString()].join('/')};
  if(!start_date){
    return object['points'][0]['C_p'];
  }else if(!end_date){
    daysArray = [dateToString(start_date)];
  }else{
    var getDaysArray = function(s,e) {for(var a=[],d=s;d<=e;d.setDate(d.getDate()+1)){ a.push(new Date(d));}return a;};
    daysArray = getDaysArray(start_date, end_date).map(function(d){return dateToString(d)});
  }
  var rows = daysArray.map(function(o1) {var filtered = object['points'].filter(function(o2){return o2['D_p'] === o1}); return filtered.length ? filtered[0] : null});
  return rows.map(function(o){return o ? o['C_p'] : o});
}

 

[ramiv]

אהלן,


@liranviper - האם התבנית עובדת לך? אני ניסיתי להיכנס היום ולא הצלחתי. שורש הבעיה לדעתי היא שהנוסחה הבאה כבר לא עובדת בעבור המחיר.

=IF(F3 = "", "", IF(E3 = "ארה""ב", M3*dollar_rate, REGEXEXTRACT(INDEX(IMPORTHTML(CONCATENATE("http://finance.sponser.co.il/finance/table/?s=",B3), "table", 5), 1, 1), "\S*$")/100))

יש דרך לסדר?

 

[liranviper]

@ramiv
נראה שהלינק באמת לא עובד יותר. לדוגמא: http://finance.sponser.co.il/finance/table/?s=5111240
אתה יכול לנסות אולי את השיטה של @LMAO תגובה אחת לפניך

אם אני לא טועה גם מישהו אחר בפורום, אולי אפילו בשרשור הזה, נתן שיטה אחרת למשיכת מחיר של ישראליות

 

[Yevgeny]

יש דרך לסדר?

אם אני לא טועה גם מישהו אחר בפורום, אולי אפילו בשרשור הזה, נתן שיטה אחרת למשיכת מחיר של ישראליות

ציינתי לפני כמה שנים בפורום כמה נוסחאות שאני משתמש בהן למשוך מחיר עבור ני"ע ישראליים (אם הראשונה לא מצליחה, מנסה מהמקור השני וכו'). לא זוכר אם משהו השתנה מאז:

בגיליון הגוגל שבו אני מנהל את התיק שלי אני מושך מעט נתונים מאתרים חיצוניים - בעיקר רק את המחיר - אבל עבור ני"ע ישראליים (קרנות) מימשתי את זה ע"י כך שאני סדרתית מנסה למשוך ממספר מקורות. אם הראשון נכשל, מנסה מהשני, והלאה.

 

[ramiv]

@ramiv
נראה שהלינק באמת לא עובד יותר. לדוגמא: http://finance.sponser.co.il/finance/table/?s=5111240
אתה יכול לנסות אולי את השיטה של @LMAO תגובה אחת לפניך

אם אני לא טועה גם מישהו אחר בפורום, אולי אפילו בשרשור הזה, נתן שיטה אחרת למשיכת מחיר של ישראליות

ואללה חזר לעבוד. אבל לא הכי אמין כפי שאפשר לראות.

@liranviper - אתה מציג את שער הפתיחה בגיליון שלך? או את שער הבסיס (והאם יש הבדל בינהם)

 

[ramiv]

ציינתי לפני כמה שנים בפורום כמה נוסחאות שאני משתמש בהן למשוך מחיר עבור ני"ע ישראליים (אם הראשונה לא מצליחה, מנסה מהמקור השני וכו'). לא זוכר אם משהו השתנה מאז:

תודה רבה, איישם בפעם הבאה שלא יעבוד

 

[liranviper]

2 מהנוסחאות של @Yevgeny החזירו אותה תוצאה - בנק לאומי וספונסר
של הבורסה נכשלת
ושל גלובוס מחזירה תוצאה אחרת (אולי זה פתיחה לעומת סגירה או משהו כזה, אבל אולי גם באג)

בכל מקרה, נוסחה אחידה שמנסה את ספונסר ואם לא מצליח אז מנסה את בנק לאומי:

=IFERROR(REGEXEXTRACT(INDEX(IMPORTHTML(CONCATENATE("http://finance.sponser.co.il/finance/table/?s=",B8), "table", 5), 1, 1), "\S*$"), VALUE(importXML(CONCATENATE("http://trade.bankleumi.co.il/trade/net/info/funds/FundDetails.aspx?fid=",B8),"(//td[@id='UnitPrice']/text())[1]")))

 

[ramiv]

2 מהנוסחאות של @Yevgeny החזירו אותה תוצאה - בנק לאומי וספונסר
של הבורסה נכשלת
ושל גלובוס מחזירה תוצאה אחרת (אולי זה פתיחה לעומת סגירה או משהו כזה, אבל אולי גם באג)

בכל מקרה, נוסחה אחידה שמנסה את ספונסר ואם לא מצליח אז מנסה את בנק לאומי:

תוכל בבקשה לעדכן את הנוסחה כך שתעבוד בשקלים בשני המקרים? לדעתי הנוסחה מחזירה כרגע באגורות.

עריכה: הוספתי את זה בעצמי. להבנתי שני האתרים מספקים נתונים באגורות. מכיוון שהתבנית בנויה על שקלים הנה היא:

=IFERROR(REGEXEXTRACT(INDEX(IMPORTHTML(CONCATENATE("http://finance.sponser.co.il/finance/table/?s=",B3), "table", 5), 1, 1), "\S*$")/100, VALUE(importXML(CONCATENATE("http://trade.bankleumi.co.il/trade/net/info/funds/FundDetails.aspx?fid=",B3),"(//td[@id='UnitPrice']/text())[1]"))/100)

 

[Yevgeny]

פתחתי את הגליון הנוכחי שלי, ונראה שעשיתי תיקונים לאורך השנים, בנוסחאות ובבדיקה אם היו שגיאות בשליפה הקודמת.
בדוגמאות הבאות:
* עמודה R זה שדה בוליאני שאומר האם זה נייר שקלי
* עמודה AA זה המספר של הנייר/קרן
* עמודה AB זה מזהה של הני"ע שספציפי לגלובס
* עמודה T זה שליפה מספונסר
* עמודה U זה שליפה מהבורסה
* עמודה V זה שליפה מלאומי
* עמודה W זה שליפה מגלובס

של הבורסה נכשלת

בנוסחה של הבורסה אני חושב שהשינוי הוא שבסוף יש [2] במקום [1].

=IF(AND($R12,OR(ISERROR(T12), VALUE(T12)<=0)), importXML(CONCATENATE("http://info.tase.co.il/heb/General/trustfund/Pages/fundmaindata.aspx?FundID=",$AA12),"(//td[@class='IndexesValuesRed']/text() | //td[@class='IndexesValuesGreen']/text())[2]"), T12)

ושל גלובוס מחזירה תוצאה אחרת (אולי זה פתיחה לעומת סגירה או משהו כזה, אבל אולי גם באג)

במקרה של גלובס, נראה שיש שינוי גם בנוסחא וגם ה-ID של כל ני"ע ישראלי הוא שונה אצלם (ואני שומר אותו בעמודה נוספת בגליון).
למשל הקרן הישראלית שמספרה 5118393 מזוהה בגלובס עם מזהה 85049: https://www.globes.co.il/portal/instrument.aspx?instrumentid=85049

=IF(AND($R12,OR(ISERROR(V12), VALUE(V12)<=0)), INDEX(importHTML(CONCATENATE("http://www.globes.co.il/portal/instrument.aspx?instrumentid=",$AB12),"table", 2),10,2), V12)

למען הסדר הטוב, השליפה מספונסר היא עדיין:

=IF(R12, REGEXEXTRACT(INDEX(IMPORTHTML(CONCATENATE("http://finance.sponser.co.il/finance/table/?s=",$AA12), "table", 5), 1, 1), "\S*$"))

והשליפה מלאומי היא עדיין:

=IF(AND($R12,OR(ISERROR(U12), VALUE(U12)<=0)), VALUE(importXML(CONCATENATE("http://trade.bankleumi.co.il/trade/net/info/funds/FundDetails.aspx?fid=",$AA12),"(//td[@id='UnitPrice']/text())[1]")), U12)

 

[liranviper]

העלתי תבנית מעודכנת (תיקוני באגים, נוסחת משיכת ערך מניות מעודכנת ועוד)
https://docs.google.com/spreadsheets/d/12GXs79EIcE1Ov9fdqCiVekdqYhPIv3omjs4HayLNMhM/edit#gid=0

 

[ramiv]

תודה לירן.

תוכל בבקשה להסביר את האזהרה בגיליון "תיק" - "לא להמיר ישר - לבדוק איזון. לבדוק אם מחיר יחידה 0 ש"ח"?

חשבתי שצריך להכניס יתרה שאני מתכוון להפקיד.

בנוסף - תא F21 - דולרים לקנייה ריק. אני מילאתי אותו ע"י:
ROUNDUP((J22+K22)/dollar_conv_rate) - balance_dollar

כאשר J22,K22 - סה"כ ניירות אמריקאים (בש"ח) + עמלות בש"ח. חלקי עלות רכישת דולרים פחות יתרה דולרית בחשבון.

 

[liranviper]

תוכל בבקשה להסביר את האזהרה בגיליון "תיק" - "לא להמיר ישר - לבדוק איזון. לבדוק אם מחיר יחידה 0 ש"ח"?

לוודא האם יש צורך לקנות אג"ח ישראלי בשקלים, לפני שממירים ישר את כל השקלים לדולרים לצורך קניית נ"ע אמריקאים.
היה איזה באג באתר שממנו משכתי את הנתונים, שגרם לכך שמחיר היחידה שחושבו ממנו האחוזים הנדרשים לאיזון היה 0 ש"ח, מה שעיוות את החישוב. אז כתבתי לשים לב לזה.

חשבתי שצריך להכניס יתרה שאני מתכוון להפקיד.

אתה יכול להזין או שקלים או דולרים לבחירתך.. אני לריב ממיר את השקלים ואז מזין את הסכום בדולרים לאחר ההמרה

 

[ramiv]

@liranviper איך אתה מכניס דיווידנדים? נניח וקרן / מנייה מחלקת דיוידנד, המחיר שלה יורד. איפה נכון להכניס את כניסת הדיוידנד?

בנוסף, האם אתה מתייחס להפקדות בעבר זה היה מוגדר חיובי (צבוע בירוק) ובגרסה הנוכחית אני רואה שאתה צובע באדום. אני רוצה לעשות שימוש במסך הפעולות כדי לדעת בסוף שנה / תקופה כמה התיק שלי עשה באמעצות XIRR. איך אתה משתמש בו?

תודה,
רמי

 

[liranviper]

@liranviper איך אתה מכניס דיווידנדים? נניח וקרן / מנייה מחלקת דיוידנד, המחיר שלה יורד. איפה נכון להכניס את כניסת הדיוידנד?

בנוסף, האם אתה מתייחס להפקדות בעבר זה היה מוגדר חיובי (צבוע בירוק) ובגרסה הנוכחית אני רואה שאתה צובע באדום. אני רוצה לעשות שימוש במסך הפעולות כדי לדעת בסוף שנה / תקופה כמה התיק שלי עשה באמעצות XIRR. איך אתה משתמש בו?

תודה,
רמי

מתעד דיבידנדים בטאב פעולות
אף פעם לא הכנסתי שם הפקדות בירוק או אדום אז אין לי מושג על מה מדובר.. יש למעלה טבלה נוספת שבה אני מתעד הפקדות ברמה רבעונית

יש לי בטאב פעולות למטה חישוב xirr

 

[ramiv]

מתעד דיבידנדים בטאב פעולות

כיצד? כמות בדולר למנייה? תוכל להעלות צילום מסך של שורה לדוגמה שאבין?

אף פעם לא הכנסתי שם הפקדות בירוק או אדום אז אין לי מושג על מה מדובר..

התכוונתי שבגרסה שאני משתמש בה (מבוססת על גיליון מיושן יותר שלך) קנייה מסומנת כחיובי וצבועה בירוק ומכירה בשלילי וצבועה באדום. ובגרסה הנוכחית שלך קנייה מסומנת בשלילי וצבועה באדום.