ตารางของเนื้อหา ในการนี้ชุดสามส่วนหรือบทความการวิเคราะห์ทางเทคนิคใน Excel เราจะสำรวจว่าผู้ค้าสามารถใช้ Excel เพื่อนำไปใช้การวิเคราะห์ทางเทคนิค (TA) เพื่อข้อมูลการตลาดทางประวัติศาสตร์ ซึ่งจะรวมถึงการคำนวณของบางส่วนของตัวชี้วัดการวิเคราะห์ทางเทคนิคที่นิยมมากที่สุดและการดำเนินการกลยุทธ์การซื้อขาย backtesting สเปรดชีต (ในส่วนที่สาม) backtesting จะเกี่ยวข้องกับการสร้างสัญญาณซื้อและขายตามตัวชี้วัด TA และการคำนวณของกลยุทธ์ P & L พต้องการชี้ให้เห็นล่วงหน้าว่าการคำนวณทั้งหมดในบทความเหล่านี้จะได้รับการดำเนินการโดยใช้ฟังก์ชั่น Excel มาตรฐานที่มีอยู่ใน Excel 2011 และต่อมา เราจะไม่ใช้ใด ๆ VBA / กำหนดเองแมโคร Excel นี้จะกระทำในวัตถุประสงค์เพื่อให้สเปรดชีทที่เรียบง่ายและการทำงานที่เข้าใจได้โดยโปรแกรมเมอร์ไม่ ในส่วนแรกของซีรีส์ในบทความนี้เราจะสร้างกระดาษคำนวณ Excel ที่เราจะใช้สูตรบางตัวชี้วัดการวิเคราะห์ทางเทคนิคทั่วไปเช่น: ง่ายค่าเฉลี่ยเคลื่อนที่, แถบ Bollinger และเฉลี่ยเคลื่อนที่ที่อธิบาย เราจะอธิบายสูตรและรวมถึงคำแนะนำขั้นตอนโดยขั้นตอนดังต่อไปนี้ นอกจากนี้เรายังมีการให้ใดก็ตามเราสเปรดชีตที่สร้างขึ้นโดยขั้นตอนต่อไประบุไว้ในบทความนี้เพื่อให้คุณสามารถใช้มันสำหรับการวิเคราะห์ข้อมูลการตลาดของคุณเองหรือเป็นพื้นฐานสำหรับการสร้างสเปรดชีตของคุณเอง ไฟล์ตัวอย่าง Excel Excel ไฟล์ (ดาวน์โหลด) ที่มีสูตรในการคำนวณค่าเฉลี่ยเคลื่อนที่ง่าย Bollinger Bands และชี้แจงค่าเฉลี่ยเคลื่อนที่ที่อธิบายไว้ในบทความนี้ สำหรับตัวอย่างนี้เราได้มีไฟล์ CSV 6 เดือนของข้อมูล SPY ชั่วโมงครอบคลุม 3 กันยายน 2013 28 กุมภาพันธ์ 2014 SPY เป็นอีทีเอฟติดตามดัชนี SP500 เรามีเกือบ 2,000 จุดข้อมูลในแฟ้มนี้ ไฟล์ที่มีคอลัมน์ OHCL ราคาปริมาณและคอลัมน์ประทับเวลา หมายเหตุ: ไฟล์นี้ได้รับการสร้างขึ้นโดยใช้ข้อมูล IB ดาวน์โหลด ไฟล์ข้อมูล: historical_data_SPY_1hour_20140301 (แฟ้มข้อความดาวน์โหลดคลิกขวาและเลือกบันทึกไฟล์ที่เชื่อมโยงเป็น ... ) ย้ายง่ายเฉลี่ย การคำนวณขั้นพื้นฐาน ง่ายค่าเฉลี่ยเคลื่อนที่ (SMA) เป็นเพียงราคาเฉลี่ยมากกว่าจำนวนไม่มีข้อความล่าสุดของบาร์ ลองคำนวณ SMA สำหรับราคาที่ใกล้จากแฟ้มข้อมูลตัวอย่างของเรา เราจะคำนวณ 20 วันค่าเฉลี่ยเคลื่อนที่ขึ้นอยู่กับราคาปิด SPY (คอลัมน์ D) ช่วยเพิ่มส่วนหัวของคอลัมน์ "SMA-20" ในคอลัมน์ G และเราพิมพ์ในมูลค่าสูตรต่อไปนี้ในเซลล์ G21 (ตั้งแต่แถวที่ 21 เป็นคนแรกที่มีข้อมูลเพียงพอในการคำนวณ 20 วัน SMA): = SUM (D2: D21) / 20 หลังจากตีกลับไปบันทึกสูตรที่คุณควรจะเห็นค่า '164.57 หรือใกล้เคียงกับในเซลล์ G21 ในการคำนวณ SMA-20 สำหรับทุกเซลล์ที่เหลืออยู่ด้านล่างเพียงแค่เลือกเซลล์ G21 เลื่อนเคอร์เซอร์ไปที่เซลล์และดับเบิลคลิกที่สี่เหลี่ยมเล็ก ๆ ที่มุมขวาล่างของมือถือที่ ขณะนี้คุณควรจะเห็นค่าใน G คอลัมน์จากการคำนวณที่เหลือของราคาสายลับ generalizing SMA คำนวณ ตอนนี้เรามีการคำนวณ 20 วันที่เรียบง่ายย้ายค่าเฉลี่ยในคอลัมน์ G มันเป็นเรื่องที่ดี แต่สิ่งที่ถ้าเราต้องการคำนวณ 50 วันหรือ 200 วัน SMA ตอนนี้หรือไม่ การปรับปรุงสูตรค่าเวลาที่คุณต้องการเปลี่ยนช่วง SMA ทุกสวยน่าเบื่อและผิดพลาดได้ง่าย ขอให้การคำนวณของเราทั่วไปมากขึ้นโดยการเพิ่มพารามิเตอร์ยาว เราสามารถเริ่มต้นโดยการจัดเก็บพารามิเตอร์ช่วง SMA ในเซลล์ที่แยกต่างหากเพื่อให้เราสามารถอ้างอิงหรือสูตร นี่คือขั้นตอนที่เราใช้ในการดำเนินการคำนวณทั่วไป SMA ในกระดาษคำนวณของเรา: ช่วยให้เริ่มต้นด้วยการสร้างตารางเล็ก ๆ น้อย ๆ ในด้านที่เราสามารถจัดเก็บบางค่าพารามิเตอร์การป้อนข้อมูลสำหรับตัวชี้วัดของเรา ในเซลล์ O1 ให้ประเภทของ "ชื่อตัวแปร" ในเซลล์ชนิด P1 ช่วยให้ "คุ้มค่า" ในเซลล์ O2 ให้พิมพ์ชื่อของตัวแปรของเรา: "ระยะเวลา" ในเซลล์ P2 เราระบุค่าของ "ระยะเวลา" ตัวแปรที่เราจะใช้ในการระบุความยาวของระยะเวลาในการคำนวณ SMA ทั่วไปของเรา การเปลี่ยนตัวแปรนี้จะทำให้การคำนวณใหม่ของ SMA ด้วยค่างวดปัจจุบัน ลองใช้ค่า 14 สำหรับในตอนนี้ พิมพ์ขอค่าส่วนหัวของคอลัมน์ "SMA" ในเซลล์ H1; H คอลัมน์จะมีค่าสำหรับตัวบ่งชี้ SMA เราทั่วไป ใน H2 เซลล์ป้อนสูตรนี้: = SUM (OFFSET (H2 (- 1 * $ P $ 2 + 1) - 4, $ P $ 2,1)) / $ P $ 2 ลองผ่าสูตรนี้ ขณะนี้เรากำลังใช้ค่าของตัวแปรระยะเวลาของเราจากเซลล์ P2 เรามีการเพิ่ม $ ในด้านหน้าของคอลัมน์และตัวเลขแถวที่จะหยุดการอ้างอิงถึง P2 มือถือที่เราคัดลอก SMA สูตรไปยังเซลล์อื่น ๆ ในคอลัมน์เอชเรายังได้ถูกแทนที่ด้วยการอ้างอิงที่แน่นอนที่จะปิดช่วงราคาคอลัมน์ที่มีฟังก์ชั่น OFFSET Excel ชดเชยผลตอบแทนช่วงของเซลล์บนพื้นฐานของการชดเชยในแง่ของจำนวนแถวและคอลัมน์จากการอ้างอิงเซลล์ที่กำหนด พารามิเตอร์แรกคือการอ้างอิงเซลล์ (ในกรณีของเรา H2 ตัวเอง) ที่สองคือการแสดงออกการคำนวณแถวแรกของช่วงขึ้นอยู่กับค่าของพารามิเตอร์ยาว ($ P $ 2) พารามิเตอร์ 3 เป็นคอลัมน์ชดเชยคอลัมน์ปิด (- 4) ค่าลบหมายถึงการชดเชยทางด้านซ้ายในขณะที่ในเชิงบวกที่จะชดเชยไปทางขวาของการอ้างอิงเซลล์และพารามิเตอร์ของฟังก์ชันสุดท้ายที่มีมูลค่า 1 แสดงให้เห็นถึงความกว้างของช่วงที่ส่งกลับโดยฟังก์ชั่น OFFSET ซึ่งในกรณีของเราเป็นเพียงหนึ่งในคอลัมน์: D (ปิด) ประหยัดค่าใช้สูตรในเซลล์ใน H2 และขยายไปยังส่วนที่เหลือของเซลล์ในคอลัมน์ H โดยการดับเบิลคลิกที่ตารางเล็ก ๆ น้อย ๆ ที่มุมขวาล่างของเซลล์หรือลากสูตรลง ลบข้อผิดพลาดสูตร ตอนนี้คุณจะสังเกตเห็นว่าหลายแถวแรกในคอลัมน์ที่มีค่าความผิดพลาด #REF นี้เกิดขึ้นเพราะมีไม่เพียงพอในแถวข้อมูลของเราตั้งในการคำนวณค่า SMA และช่วงที่ส่งกลับโดยฟังก์ชั่น OFFSET ไปเหนือขอบของแผ่นงานสำหรับแถวบาง มีจำนวนของเทคนิคต่างๆที่จะซ่อนค่าความผิดพลาดใน Excel ที่มีอยู่ บางส่วนของพวกเขาเกี่ยวข้องกับสูตรที่คืนค่าที่ว่างเปล่าหรือศูนย์ถ้าค่ามือถือที่มีข้อผิดพลาด ขณะนี้เป็นที่ถูกต้องสมบูรณ์ technique - มันซับซ้อนสูตรในเซลล์และทำให้พวกเขายากที่จะอ่าน แต่เราจะใช้รูปแบบตามเงื่อนไขที่จะเพียงแค่ซ่อนค่าความผิดพลาดจะมีการเปลี่ยนแปลงสีพื้นเป็นสีขาว ในการเปลี่ยนสีตัวอักษรของเซลล์เป็นสีขาวและใช้ไม่มีข้อผิดพลาดการเน้นปฏิบัติตามคำแนะนำเหล่านี้: เลือกคอลัมน์ H-N ใน Excel: บ้าน - & gt; เงื่อนไขการจัดรูปแบบ - & gt; เน้นกฎมือถือ - & gt; กฎเพิ่มเติม ใน "การจัดรูปแบบใหม่กฎโต้ตอบ" เลือก "ข้อผิดพลาด" และ "รูปแบบที่มี ... " เลือก "รูปแบบที่กำหนดเอง" แล้วตั้งเติมสีให้เป็นสีขาวและตัวอักษรเป็นสีขาวเช่นกัน Bollinger Bands บทนำ แถบ Bollinger เป็นตัวบ่งชี้ที่เรียบง่าย แต่มีประโยชน์ให้ข้อมูลที่มีค่าเกี่ยวกับความผันผวนของราคาในอดีตของเครื่องมือทางการเงินเช่นเดียวกับราคาปัจจุบันเบี่ยงเบนจากค่าเฉลี่ยเคลื่อนที่ เมื่อราคาการเคลื่อนไหวผันผวนมากขึ้นกลายเป็นวงกว้างในช่วงเวลาของความสงบญาติพวกเขามาใกล้ชิดกัน ตำแหน่งสัมพัทธ์ของราคาปัจจุบันวงดนตรีนอกจากนี้ยังสามารถใช้ในการประมาณการว่าตลาดดุลหรือขาดดุล หากราคาปัจจุบันอยู่ใกล้กับหรือข้ามวงบนราคามีการพิจารณาในดินแดน overbought ในขณะที่ราคาใกล้เคียงกับ / ข้ามตลาดที่ต่ำกว่าพื้นฐานวงถือว่าเป็น oversold การคำนวณขั้นพื้นฐาน ตัวบ่งชี้ที่ Bollinger Bands จะได้รับการคำนวณโดยใช้ง่ายย้ายค่าเฉลี่ยเคลื่อนที่หรือชี้แจงเฉลี่ยเป็นพื้นฐาน แถบ Bollinger ประกอบด้วยสามชุดข้อมูล: ค่าเฉลี่ยเคลื่อนที่ (ง่ายหรือชี้แจง) และสองส่วนเบี่ยงเบนมาตรฐาน (เขตแดน) สายเหนือและต่ำกว่าค่าเฉลี่ยเคลื่อนที่ปกติจะอยู่ที่ 2 ส่วนเบี่ยงเบนมาตรฐานจากการย้ายค่าเฉลี่ย ชี้แจงค่าเฉลี่ยเคลื่อนที่ (ปกคลุมด้านล่าง) ให้น้ำหนักมากขึ้นในการดำเนินการที่ผ่านมาราคาขึ้นในขณะที่ค่าเฉลี่ยเคลื่อนที่ที่เรียบง่ายมีตัวบ่งชี้ที่มีเสถียรภาพมากขึ้นและกระวนกระวายใจน้อย มีทั้งหมด 2 ป้อนพารามิเตอร์คือ 1) การเคลื่อนย้ายระยะเวลาเฉลี่ย (จำนวนบาร์) 2) จำนวนค่าเบี่ยงเบนมาตรฐานของวงดนตรีวงดนตรีบนที่ต่ำกว่า ในตัวอย่างนี้เราจะใช้ค่าเฉลี่ยเคลื่อนที่ง่ายที่เราคำนวณแล้วในคอลัมน์ H (ดูคำแนะนำในส่วนด้านบน) สิ่งที่เหลือคือการเพิ่มคอลัมน์สำหรับวงดนตรีบนและล่าง เรายังคงใช้ 14 วันระยะเวลาการย้ายค่าเฉลี่ย แถวแรกที่มีข้อมูลที่เพียงพอสำหรับการ SMA 14 วันคือ 15 แถว (ตั้งแต่ 1 แถวจะใช้สำหรับส่วนหัวของคอลัมน์) วงดนตรีบนจะอยู่ในคอลัมน์ผมดังนั้นในเซลล์ I15 เราพิมพ์สูตรต่อไปนี้: = H15 + 2 * STDEV (D2: D15) ในสูตรนี้เราจะเป็นเพียงการเพิ่มสองค่าเบี่ยงเบนมาตรฐานของราคาปิดจากเซลล์ D2: D15 ค่า SMA = H15-2 * STDEV (D2: D15) ที่นี่แตกต่างจากสูตรก่อนหน้านี้ว่าเราจะลบสองค่าเบี่ยงเบนมาตรฐานจาก SMA สูตร Excel STDEV () คำนวณค่าเบี่ยงเบนมาตรฐานสำหรับชุดของค่า ในกรณีนี้เรามีการคูณค่า 2 ที่จะได้รับ 2 ส่วนเบี่ยงเบนมาตรฐานและการเพิ่ม / ลบผลจากค่าเฉลี่ยที่จะย้ายไปสร้างบน / ต่ำกว่าค่าวงดนตรี เพื่อขยายสูตรเพียงเกลือกกลิ้งและดับเบิลคลิกที่สี่เหลี่ยมเล็ก ๆ ที่มุมขวาล่างของเซลล์ที่จะทำซ้ำสูตรสำหรับส่วนที่เหลือของช่วงข้อมูล คำนวณทั่วไป Bollinger วง ตอนนี้ วิธีการเกี่ยวกับ generalizing สูตรวง Bollinger เพื่อที่เราจะได้มีการปรับปรุงสูตรของเราทุกครั้งที่เราต้องการคำนวณแถบ Bollinger สำหรับจำนวนที่แตกต่างกันของค่าเบี่ยงเบนมาตรฐานจากแมสซาชูเซตหรือเมื่อเราเปลี่ยนย้ายความยาวเฉลี่ย ลองเพิ่มพารามิเตอร์อื่นในตารางตัวแปรทั่วไปของเราบนด้านขวาของกระดาษคำนวณ ลองพิมพ์ "devs Std" ในเซลล์ O3, และ 2.0 ใน P3 ถัดไปช่วยเพิ่มสูตรต่อไปนี้ใน I15: = H15 + $ P $ 3 * STDEV (OFFSET (I15 (- 1 * $ P $ 2 + 1) - 5, $ P $ 2,1)) ในสูตรนี้เราได้แทนที่ 2 $ P $ 3 ซึ่งชี้ไปยังตัวแปรของเราในเซลล์ P3 ที่มีจำนวนค่าเบี่ยงเบนมาตรฐานสำหรับวงดนตรีและคำนวณชดเชยขึ้นอยู่กับตัวแปรระยะเวลาในเซลล์ P2 = H15- $ P $ 3 * STDEV (OFFSET (J15 (- 1 * $ P $ 2 + 1) - 6, $ P $ 2,1)) เฉพาะที่แตกต่างจากสูตรในขั้นตอนก่อนหน้านี้คือการที่เราได้เข้ามาแทนที่หลังจาก H15 + กับ (ลบ) เพื่อลบจำนวนค่าเบี่ยงเบนมาตรฐานจาก SMA และเรามีการเปลี่ยนแปลงเพื่อชดเชย columnd ราคา สังเกตเห็น -6 แทน -5 ใน "คอลัมน์" พารามิเตอร์ไปยังฟังก์ชัน OFFSET เพื่ออ้างถึงคอลัมน์ D (ปิด) อย่าลืมที่จะคัดลอกสูตรใหม่ในเซลล์ I15 J15 และส่วนที่เหลือของเซลล์คอลัมน์ที่เกี่ยวข้อง ขณะนี้คุณสามารถเปลี่ยนค่าของ "ระยะเวลา" และ "Std devs" ตัวแปรในเซลล์ P2 P3 และมีการ SMA และค่า Bollinger วงคำนวณใหม่โดยอัตโนมัติ แถบ Bollinger แผนภูมิใน Excel ดูวิดีโอนี้มีคำแนะนำสำหรับการเพิ่มแผนภูมิ Bollinger วงสเปรดชีตเราสร้างขึ้นดังกล่าวข้างต้น เฉลี่ยเคลื่อนที่ที่อธิบาย เฉลี่ยเคลื่อนที่ที่อธิบาย (EMA) เป็นชนิดของค่าเฉลี่ยเคลื่อนที่ที่คล้ายกับค่าเฉลี่ยเคลื่อนที่ง่ายยกเว้นว่าน้ำหนักมากขึ้นจะได้รับข้อมูลล่าสุด ค่าเฉลี่ยเคลื่อนที่ชี้แจงเป็นที่รู้จักกันชี้แจงน้ำหนักเฉลี่ยเคลื่อนที่ คำแนะนำในการคำนวณ เราจะใช้ K คอลัมน์ในการคำนวณ EMA ลองตั้งค่าระยะเวลาของเราถึง 1 (เซลล์ P2) เพื่อให้เราสามารถใส่สูตรที่ด้านบนของแผ่นของเราและมีค่าบางอย่างที่เราสามารถมองเห็นเข้าสูตร เราสามารถกำหนดระยะเวลาเพื่อให้มีค่าใด ๆ หลังจากที่เรากำลังทำและมี EMA (และ SMA) คำนวณใหม่โดยอัตโนมัติ ในเซลล์ K2 ที่เราตั้งค่าแรกของซีรีส์ EMA ที่จะเป็นเพียงแค่เท่ากับราคาปิด (D2) ในแถวเดียวกันเพียงเพราะเราต้อง "เมล็ดพันธุ์" คำนวณ EMA กับบางส่วนมูลค่าที่เหมาะสม ถัดไปในเซลล์ K3 เราใส่สูตร EMA มาตรฐานที่ใช้ฟังก์ชั่นสัญลักษณ์มาตรฐานอุตสาหกรรม 2 / (1 + จำนวนของระยะเวลาในแมสซาชูเซต) เพื่อทำความเข้าใจคณิตศาสตร์ที่อยู่เบื้องหลังนี้ดูที่หน้านี้ = D3 * 2 / (1 + $ P $ 2) + K2 * (1-2 / (1 + $ P $ 2)) ในสูตรนี้เราคูณราคาปิดของแถว (D3) โดยฟังก์ชั่นสัญลักษณ์ที่ใช้ $ P $ 2 ถึงการอ้างอิง "จำนวนของระยะเวลาของเรา" ตัวแปรและเพิ่มผลค่า EMA ก่อนหน้า (K2) คูณ "1- ตัวแทน" . นี่คือสูตร EMA มาตรฐาน ตอนนี้ขยายสูตรที่เหลือของคอลัมน์โดยคลิกที่ตารางด้านขวาล่างของเซลล์ K3 ตอนนี้เราสามารถเปลี่ยนค่างวดไปยังหมายเลขอื่น ๆ ให้แน่ใจว่าการปกครองรูปแบบตามเงื่อนไขของคุณมีการปรับปรุงเพื่อซ่อนแสดงค่าความผิดพลาดในเซลล์ที่ไม่ได้มีข้อมูลเพียงพอที่จะกลับไปคำนวณค่าของพวกเขา ส่วนที่สรุป ในส่วนนี้เป็นครั้งแรกของซีรีส์ 3 เป็นส่วนหนึ่งของเราที่เราคำนวณการย้ายง่ายเฉลี่ย, Bollinger Bands และเคลื่อนที่ที่อธิบายตัวชี้วัดการวิเคราะห์ทางเทคนิคเฉลี่ยสำหรับข้อมูลชุดตัวอย่างของเราในอดีต ในส่วนต่อไปเราจะครอบคลุมสองของตัวชี้วัดการวิเคราะห์ทางเทคนิคที่มีชื่อเสียงที่สุด: MACD และ RSI ตัวอย่างในบทความชุดนี้ไม่ได้หมายความว่าจะนำเสนอกลยุทธ์การซื้อขายใด ๆ ที่จริง แต่นี้เป็นเพียงการสาธิตวิธีผู้ประกอบการสามารถใช้ Excel ในการวิเคราะห์ข้อมูลทางประวัติศาสตร์และความคิดของเขา backtest / กลยุทธ์การซื้อขายของเธอเองโดยใช้ Microsoft Excel